Creation works but Deletion is not working after successful job run

[Rammurthy5]

Here is the table schema,

Column   |            Type             | Collation | Nullable |      Default
-----------+-----------------------------+-----------+----------+-------------------
 name      | character varying(50)       |           |          |
 uuid      | uuid                        |           | not null | gen_random_uuid()
 tscreated | timestamp without time zone |           | not null | LOCALTIMESTAMP
Partition key: RANGE (tscreated)
Indexes:
    "emp_pkey" PRIMARY KEY, btree (uuid, tscreated)
Number of partitions: 7 (Use \d+ to list them.)`

cron job to call run_maintenance_proc() is set to hourly.
retentions are set to 3 hours.
retention_keep_table and retention_keep_index are set to False

part_config

   parent_table     |  control  | partition_interval | partition_type | premake | automatic_maintenance |            template_table            | retention
| retention_schema | retention_keep_index | retention_keep_table | epoch | constraint_cols | optimize_constraint | infinite_time_partitions |  datetime_strin
g  | jobmon | sub_partition_set_full | undo_in_progress | inherit_privileges | constraint_valid | ignore_default_data | default_table | date_trunc_interval
----------------------+-----------+--------------------+----------------+---------+-----------------------+--------------------------------------+-----------
+------------------+----------------------+----------------------+-------+-----------------+---------------------+--------------------------+----------------
---+--------+------------------------+------------------+--------------------+------------------+---------------------+---------------+---------------------
db_owner.emp | tscreated | 01:00:00           | range          |       3 | on                    | public.template_db_owner_emp | 3 hours
|                  | f                    | f                    | none  |                 |                  30 | t                        | YYYYMMDD_HH24MI
SS | t      | f                      | f                | f                  | t                | t                   | f             |
(1 row)

pg 15.8
partman 5.0.1-1.
run_maintenance is successful. no errors seen.

what am i doing wrong?

[keithf4]

Do you have any data the table? Maintenance generally doesn't do anything when a table is completely empty of all data (except if you have infinite_time_partitions set to true in part_config. I tested it out and it did nothing when there was no data. As soon as I inserted some data, then both new table creation and retention worked

github700=# create table db_owner.emp (name varchar(50), uuid uuid default gen_random_uuid(), tscreated timestamp without time zone not null) partition by range (tscreated);
CREATE TABLE
github700=# alter table db_owner.emp add primary key (uuid, tscreated);
ALTER TABLE

github700=# select partman.create_parent('db_owner.emp', 'tscreated', '1 hour', p_start_partition := '2024-10-24 0100');
 create_parent 
---------------
 t
(1 row)

github700=# \d+ db_owner.emp
                                                     Partitioned table "db_owner.emp"
  Column   |            Type             | Collation | Nullable |      Default      | Storage  | Compression | Stats target | Description 
-----------+-----------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
 name      | character varying(50)       |           |          |                   | extended |             |              | 
 uuid      | uuid                        |           | not null | gen_random_uuid() | plain    |             |              | 
 tscreated | timestamp without time zone |           | not null |                   | plain    |             |              | 
Partition key: RANGE (tscreated)
Indexes:
    "emp_pkey" PRIMARY KEY, btree (uuid, tscreated)
Partitions: db_owner.emp_p20241024_010000 FOR VALUES FROM ('2024-10-24 01:00:00') TO ('2024-10-24 02:00:00'),
            db_owner.emp_p20241024_020000 FOR VALUES FROM ('2024-10-24 02:00:00') TO ('2024-10-24 03:00:00'),
            db_owner.emp_p20241024_030000 FOR VALUES FROM ('2024-10-24 03:00:00') TO ('2024-10-24 04:00:00'),
            db_owner.emp_p20241024_040000 FOR VALUES FROM ('2024-10-24 04:00:00') TO ('2024-10-24 05:00:00'),
            db_owner.emp_p20241024_050000 FOR VALUES FROM ('2024-10-24 05:00:00') TO ('2024-10-24 06:00:00'),
            db_owner.emp_p20241024_060000 FOR VALUES FROM ('2024-10-24 06:00:00') TO ('2024-10-24 07:00:00'),
            db_owner.emp_p20241024_070000 FOR VALUES FROM ('2024-10-24 07:00:00') TO ('2024-10-24 08:00:00'),
            db_owner.emp_p20241024_080000 FOR VALUES FROM ('2024-10-24 08:00:00') TO ('2024-10-24 09:00:00'),
            db_owner.emp_p20241024_090000 FOR VALUES FROM ('2024-10-24 09:00:00') TO ('2024-10-24 10:00:00'),
            db_owner.emp_p20241024_100000 FOR VALUES FROM ('2024-10-24 10:00:00') TO ('2024-10-24 11:00:00'),
            db_owner.emp_p20241024_110000 FOR VALUES FROM ('2024-10-24 11:00:00') TO ('2024-10-24 12:00:00'),
            db_owner.emp_p20241024_120000 FOR VALUES FROM ('2024-10-24 12:00:00') TO ('2024-10-24 13:00:00'),
            db_owner.emp_p20241024_130000 FOR VALUES FROM ('2024-10-24 13:00:00') TO ('2024-10-24 14:00:00'),
            db_owner.emp_p20241024_140000 FOR VALUES FROM ('2024-10-24 14:00:00') TO ('2024-10-24 15:00:00'),
            db_owner.emp_p20241024_150000 FOR VALUES FROM ('2024-10-24 15:00:00') TO ('2024-10-24 16:00:00'),
            db_owner.emp_p20241024_160000 FOR VALUES FROM ('2024-10-24 16:00:00') TO ('2024-10-24 17:00:00'),
            db_owner.emp_p20241024_170000 FOR VALUES FROM ('2024-10-24 17:00:00') TO ('2024-10-24 18:00:00'),
            db_owner.emp_p20241024_180000 FOR VALUES FROM ('2024-10-24 18:00:00') TO ('2024-10-24 19:00:00'),
            db_owner.emp_p20241024_190000 FOR VALUES FROM ('2024-10-24 19:00:00') TO ('2024-10-24 20:00:00'),
            db_owner.emp_p20241024_200000 FOR VALUES FROM ('2024-10-24 20:00:00') TO ('2024-10-24 21:00:00'),
            db_owner.emp_default DEFAULT

github700=# update partman.part_config set retention = '3 hours', retention_keep_table = false where parent_table = 'db_owner.emp';
UPDATE 1

github700=# update partman.part_config set premake = 5 where parent_table = 'db_owner.emp';
UPDATE 1

github700=# call partman.run_maintenance_proc();
CALL
github700=# \d+ db_owner.emp
                                                     Partitioned table "db_owner.emp"
  Column   |            Type             | Collation | Nullable |      Default      | Storage  | Compression | Stats target | Description 
-----------+-----------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
 name      | character varying(50)       |           |          |                   | extended |             |              | 
 uuid      | uuid                        |           | not null | gen_random_uuid() | plain    |             |              | 
 tscreated | timestamp without time zone |           | not null |                   | plain    |             |              | 
Partition key: RANGE (tscreated)
Indexes:
    "emp_pkey" PRIMARY KEY, btree (uuid, tscreated)
Partitions: db_owner.emp_p20241024_010000 FOR VALUES FROM ('2024-10-24 01:00:00') TO ('2024-10-24 02:00:00'),
            db_owner.emp_p20241024_020000 FOR VALUES FROM ('2024-10-24 02:00:00') TO ('2024-10-24 03:00:00'),
            db_owner.emp_p20241024_030000 FOR VALUES FROM ('2024-10-24 03:00:00') TO ('2024-10-24 04:00:00'),
            db_owner.emp_p20241024_040000 FOR VALUES FROM ('2024-10-24 04:00:00') TO ('2024-10-24 05:00:00'),
            db_owner.emp_p20241024_050000 FOR VALUES FROM ('2024-10-24 05:00:00') TO ('2024-10-24 06:00:00'),
            db_owner.emp_p20241024_060000 FOR VALUES FROM ('2024-10-24 06:00:00') TO ('2024-10-24 07:00:00'),
            db_owner.emp_p20241024_070000 FOR VALUES FROM ('2024-10-24 07:00:00') TO ('2024-10-24 08:00:00'),
            db_owner.emp_p20241024_080000 FOR VALUES FROM ('2024-10-24 08:00:00') TO ('2024-10-24 09:00:00'),
            db_owner.emp_p20241024_090000 FOR VALUES FROM ('2024-10-24 09:00:00') TO ('2024-10-24 10:00:00'),
            db_owner.emp_p20241024_100000 FOR VALUES FROM ('2024-10-24 10:00:00') TO ('2024-10-24 11:00:00'),
            db_owner.emp_p20241024_110000 FOR VALUES FROM ('2024-10-24 11:00:00') TO ('2024-10-24 12:00:00'),
            db_owner.emp_p20241024_120000 FOR VALUES FROM ('2024-10-24 12:00:00') TO ('2024-10-24 13:00:00'),
            db_owner.emp_p20241024_130000 FOR VALUES FROM ('2024-10-24 13:00:00') TO ('2024-10-24 14:00:00'),
            db_owner.emp_p20241024_140000 FOR VALUES FROM ('2024-10-24 14:00:00') TO ('2024-10-24 15:00:00'),
            db_owner.emp_p20241024_150000 FOR VALUES FROM ('2024-10-24 15:00:00') TO ('2024-10-24 16:00:00'),
            db_owner.emp_p20241024_160000 FOR VALUES FROM ('2024-10-24 16:00:00') TO ('2024-10-24 17:00:00'),
            db_owner.emp_p20241024_170000 FOR VALUES FROM ('2024-10-24 17:00:00') TO ('2024-10-24 18:00:00'),
            db_owner.emp_p20241024_180000 FOR VALUES FROM ('2024-10-24 18:00:00') TO ('2024-10-24 19:00:00'),
            db_owner.emp_p20241024_190000 FOR VALUES FROM ('2024-10-24 19:00:00') TO ('2024-10-24 20:00:00'),
            db_owner.emp_p20241024_200000 FOR VALUES FROM ('2024-10-24 20:00:00') TO ('2024-10-24 21:00:00'),
            db_owner.emp_default DEFAULT

github700=# insert into db_owner.emp (tscreated) values (now());
INSERT 0 1
github700=# insert into db_owner.emp (tscreated) values (now()+'1 hour'::interval);
INSERT 0 1
github700=# insert into db_owner.emp (tscreated) values (now()+'2 hour'::interval);
INSERT 0 1
github700=# call partman.run_maintenance_proc();
CALL
github700=# \d+ db_owner.emp
                                                     Partitioned table "db_owner.emp"
  Column   |            Type             | Collation | Nullable |      Default      | Storage  | Compression | Stats target | Description 
-----------+-----------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
 name      | character varying(50)       |           |          |                   | extended |             |              | 
 uuid      | uuid                        |           | not null | gen_random_uuid() | plain    |             |              | 
 tscreated | timestamp without time zone |           | not null |                   | plain    |             |              | 
Partition key: RANGE (tscreated)
Indexes:
    "emp_pkey" PRIMARY KEY, btree (uuid, tscreated)
Partitions: db_owner.emp_p20241024_130000 FOR VALUES FROM ('2024-10-24 13:00:00') TO ('2024-10-24 14:00:00'),
            db_owner.emp_p20241024_140000 FOR VALUES FROM ('2024-10-24 14:00:00') TO ('2024-10-24 15:00:00'),
            db_owner.emp_p20241024_150000 FOR VALUES FROM ('2024-10-24 15:00:00') TO ('2024-10-24 16:00:00'),
            db_owner.emp_p20241024_160000 FOR VALUES FROM ('2024-10-24 16:00:00') TO ('2024-10-24 17:00:00'),
            db_owner.emp_p20241024_170000 FOR VALUES FROM ('2024-10-24 17:00:00') TO ('2024-10-24 18:00:00'),
            db_owner.emp_p20241024_180000 FOR VALUES FROM ('2024-10-24 18:00:00') TO ('2024-10-24 19:00:00'),
            db_owner.emp_p20241024_190000 FOR VALUES FROM ('2024-10-24 19:00:00') TO ('2024-10-24 20:00:00'),
            db_owner.emp_p20241024_200000 FOR VALUES FROM ('2024-10-24 20:00:00') TO ('2024-10-24 21:00:00'),
            db_owner.emp_p20241024_210000 FOR VALUES FROM ('2024-10-24 21:00:00') TO ('2024-10-24 22:00:00'),
            db_owner.emp_p20241024_220000 FOR VALUES FROM ('2024-10-24 22:00:00') TO ('2024-10-24 23:00:00'),
            db_owner.emp_p20241024_230000 FOR VALUES FROM ('2024-10-24 23:00:00') TO ('2024-10-25 00:00:00'),
            db_owner.emp_default DEFAULT

[Rammurthy5]

thanks.
there are 18 records in the table.
could you confirm if i should be running "call run_maintenance_proc()" or "select run_maintenance()" or both every hour for the same table and scenario please? I have had data in some tables and some are empty.

another question - is it expected that the run_maintenance function fail if there is a data already there for respective datetime present already? for e.g. current partition is for 25-10-2024_1300. and if the parent table is now inserted data of future time in partition key and the next run fails.

[keithf4]

run_maintenance() and run_maintenance_proc() do the same thing. The only difference is that the latter is a procedure so it's able to commit a transaction after each partition set's maintenance has runs. The procedure is actually just calling the function in a loop. It's better to call the procedure if possible to reduce transaction run-time.

If you have data going into the partition set's default table because the child table does not yet exist, then yes you can expect maintenance to fail. This is inherent in PostgreSQL because you cannot have two different child tables that can contain the same data. So when maintenance tries to make a new child table and data for it is already in the default, it fails for that reason. The partition_data_proc() procedure can fix this for you by moving the data out of the default and creating the necessary child table. Note that if it's far enough in the future, you may have gaps in the child tables. Use partition_gap_fill() to fix this.