bug: partitions are not properly removed

[Ivansete-status]

Problem

It has been detected that there are alive partitions even though they are no longer attached to the main messages table.

Impact

The database size gets impacted due to this dangling partitions potentially causing less effective data that can be kept when considering a fixed size retention policy.

nwaku version/commit hash

It is uncertain when that happened. I think is already fixed because we just found two old partitions in waku.sandbox.HK, f.e.

                                                        List of relations
 Schema |              Name              |       Type        |  Owner   | Persistence | Access method |    Size    | Description 
--------+--------------------------------+-------------------+----------+-------------+---------------+------------+-------------
 public | messages                       | partitioned table | nim-waku | permanent   |               | 0 bytes    | 
 public | messages_1724202000_1724205600 | table             | nim-waku | permanent   | heap          | 38 MB      | 
 public | messages_1724374800_1724378400 | table             | nim-waku | permanent   | heap          | 45 MB      | 
 public | messages_1744588800_1744592400 | table             | nim-waku | permanent   | heap          | 48 MB      | 
 public | messages_1744592400_1744596000 | table             | nim-waku | permanent   | heap          | 50 MB      | 
 public | messages_1744596000_1744599600 | table             | nim-waku | permanent   | heap          | 48 MB      | 
 public | messages_1744599600_1744603200 | table             | nim-waku | permanent   | heap          | 11 MB      | 
 public | messages_1744603200_1744606800 | table             | nim-waku | permanent   | heap          | 8192 bytes | 
 

Another example, in status.prod.AMS, where there are more dangling partitions:

                                                        List of relations
 Schema |              Name              |       Type        |  Owner   | Persistence | Access method |    Size    | Description 
--------+--------------------------------+-------------------+----------+-------------+---------------+------------+-------------
 public | messages                       | partitioned table | nim-waku | permanent   |               | 0 bytes    | 
 public | messages_1720551600_1720555200 | table             | nim-waku | permanent   | heap          | 3259 MB    | 
 public | messages_1720555200_1720558800 | table             | nim-waku | permanent   | heap          | 310 MB     | 
 public | messages_1721055600_1721059200 | table             | nim-waku | permanent   | heap          | 926 MB     | 
 public | messages_1721185200_1721188800 | table             | nim-waku | permanent   | heap          | 1924 MB    | 
 public | messages_1721203200_1721206800 | table             | nim-waku | permanent   | heap          | 5053 MB    | 
 public | messages_1721206800_1721210400 | table             | nim-waku | permanent   | heap          | 4675 MB    | 
 public | messages_1721210400_1721214000 | table             | nim-waku | permanent   | heap          | 1919 MB    | 
 public | messages_1721239200_1721242800 | table             | nim-waku | permanent   | heap          | 173 MB     | 
 public | messages_1721242800_1721246400 | table             | nim-waku | permanent   | heap          | 206 MB     | 
 public | messages_1721325600_1721329200 | table             | nim-waku | permanent   | heap          | 111 MB     | 
 public | messages_1721379600_1721383200 | table             | nim-waku | permanent   | heap          | 275 MB     | 
 public | messages_1722359304_1722362400 | table             | nim-waku | permanent   | heap          | 8192 bytes | 
 public | messages_1722362904_1722366000 | table             | nim-waku | permanent   | heap          | 8192 bytes | 
 public | messages_1722366504_1722369600 | table             | nim-waku | permanent   | heap          | 8192 bytes | 
 public | messages_1750957200_1750960800 | table             | nim-waku | permanent   | heap          | 30 MB      | 
 public | messages_1750960800_1750964400 | table             | nim-waku | permanent   | heap          | 50 MB      | 
 public | messages_1750964400_1750968000 | table             | nim-waku | permanent   | heap          | 16 MB      | 
 public | messages_1750968000_1750971600 | table             | nim-waku | permanent   | heap          | 41 MB      | 
 public | messages_1750971600_1750975200 | table             | nim-waku | permanent   | heap          | 37 MB      | 
 public | messages_1750975200_1750978800 | table             | nim-waku | permanent   | heap          | 88 MB      | 
 public | messages_1750978800_1750982400 | table             | nim-waku | permanent   | heap          | 30 MB      | 
 public | messages_1750982400_1750986000 | table             | nim-waku | permanent   | heap          | 21 MB      | 
 public | messages_1750986000_1750989600 | table             | nim-waku | permanent   | heap          | 3824 kB    | 
 public | messages_1750989600_1750993200 | table             | nim-waku | permanent   | heap          | 39 MB      | 
 public | messages_1750993200_1750996800 | table             | nim-waku | permanent   | heap          | 4160 kB    | 
 public | messages_1750996800_1751000400 | table             | nim-waku | permanent   | heap          | 5216 kB    | 
 public | messages_1751000400_1751004000 | table             | nim-waku | permanent   | heap          | 22 MB      | 

In order to detect whether a partition is consistent is enough by just looking at its name, and the times shown there should be linked with the neighbor partitions. In the above examples, it can be seen that there are a few not time-linked partitions at the beginning.

We detected it when was running v0.36.0 but I don't think a bug is there.

---

A store node, should cleanup all possible detached partitions so that the database is consistent.