Unable to get run_maintenance() to create future partitions

[mbrancato]

I'm planning to run pg_partman without the background worker. To prepare for that, I've be just trying to manually create a test table, and then run run_maintenance() to ensure that forward-looking partitions are being created. My initial use-case was a bit more complex, so I reduced complexity for testing purposes. However, I can still not get any future partitions to be created.

For setup, I'm running this in a container, and this is my Dockerfile:

FROM postgres:15

RUN apt-get update \
      && apt-cache showpkg postgresql-$PG_MAJOR-partman \
      && apt-get install -y --no-install-recommends \
           postgresql-$PG_MAJOR-partman \
      && rm -rf /var/lib/apt/lists/*

extension data:

partitions_test=# \dx
                               List of installed extensions
    Name    | Version |   Schema   |                     Description                      
------------+---------+------------+------------------------------------------------------
 pg_partman | 5.0.0   | partman    | Extension to manage partitioned tables by time or ID
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

I'm just using the postgres user for now to connect to a test database:

CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman WITH SCHEMA partman;

CREATE TABLE public.my_table (
    id uuid NOT NULL,
    col1 integer NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
) PARTITION BY RANGE (created_at);

SELECT partman.create_parent(
    p_parent_table := 'public.my_table'
    , p_control := 'created_at'
    , p_interval := '1 hour'
    , p_start_partition := date_trunc('hour', NOW())::text
    , p_default_table := false
);

result

partitions_test=# SELECT partman.create_parent(
partitions_test(#     p_parent_table := 'public.my_table'
partitions_test(#     , p_control := 'created_at'
partitions_test(#     , p_interval := '1 hour'
partitions_test(#     , p_start_partition := date_trunc('hour', NOW())::text
partitions_test(#     , p_default_table := false
partitions_test(# );
 create_parent 
---------------
 t
(1 row)

partitions_test=# \d+ my_table                                                                                                                                                                                                                              Partitioned table "public.my_table"
   Column   |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 id         | uuid                     |           | not null |         | plain   |             |              | 
 col1       | integer                  |           | not null |         | plain   |             |              | 
 created_at | timestamp with time zone |           | not null | now()   | plain   |             |              | 
Partition key: RANGE (created_at)
Partitions: my_table_p20231023_120000 FOR VALUES FROM ('2023-10-23 12:00:00+00') TO ('2023-10-23 13:00:00+00'),
            my_table_p20231023_130000 FOR VALUES FROM ('2023-10-23 13:00:00+00') TO ('2023-10-23 14:00:00+00'),
            my_table_p20231023_140000 FOR VALUES FROM ('2023-10-23 14:00:00+00') TO ('2023-10-23 15:00:00+00'),
            my_table_p20231023_150000 FOR VALUES FROM ('2023-10-23 15:00:00+00') TO ('2023-10-23 16:00:00+00'),
            my_table_p20231023_160000 FOR VALUES FROM ('2023-10-23 16:00:00+00') TO ('2023-10-23 17:00:00+00')

I've tried both 1 hour and 1 minute intervals for testing. In practice, I'll likely use 1 day intervals. Waiting a while and running run_maintenance() seems to do nothing. The same is true if I delete the last partition and then run run_maintenance(). I'm running it this way:

SELECT partman.run_maintenance();

The output with DEBUG1 looks like this:

output logs

2023-10-23 12:18:28.494 UTC [2712] DEBUG:  Parent table possibly removed from part_config by retenion
2023-10-23 12:18:28.494 UTC [2712] CONTEXT:  PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 115 at RAISE
2023-10-23 12:18:28.495 UTC [2712] DEBUG:  show_partitions: v_parent_schema: public, v_parent_tablename: my_table, v_datetime_string: YYYYMMDD_HH24MISS
2023-10-23 12:18:28.495 UTC [2712] CONTEXT:  PL/pgSQL function show_partitions(text,text,boolean) line 53 at RAISE
     SQL statement "SELECT partition_tablename                              FROM partman.show_partitions(v_row.parent_table, p_include_default := true) LIMIT 1"
     PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 147 at SQL statement
2023-10-23 12:18:28.495 UTC [2712] DEBUG:  show_partitions: v_default_sql: SELECT n.nspname::text AS partition_schemaname
             , c.relname::text AS partition_name
             FROM pg_catalog.pg_inherits h
             JOIN pg_catalog.pg_class c ON c.oid = h.inhrelid
             JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
             WHERE h.inhparent = 'public.my_table'::regclass
             AND pg_get_expr(relpartbound, c.oid) = 'DEFAULT'
2023-10-23 12:18:28.495 UTC [2712] CONTEXT:  PL/pgSQL function show_partitions(text,text,boolean) line 69 at RAISE
     SQL statement "SELECT partition_tablename                              FROM partman.show_partitions(v_row.parent_table, p_include_default := true) LIMIT 1"
     PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 147 at SQL statement
2023-10-23 12:18:28.498 UTC [2712] DEBUG:  show_partitions: v_sql: SELECT n.nspname::text AS partition_schemaname
             , c.relname::text AS partition_name
             FROM pg_catalog.pg_inherits h
             JOIN pg_catalog.pg_class c ON c.oid = h.inhrelid
             JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
             WHERE h.inhparent = 'public.my_table'::regclass
         AND pg_get_expr(relpartbound, c.oid) != 'DEFAULT' 
             ORDER BY (regexp_match(pg_get_expr(c.relpartbound, c.oid, true), $REGEX$\(([^)]+)\) TO \(([^)]+)\)$REGEX$))[1]::text::timestamptz ASC 
2023-10-23 12:18:28.498 UTC [2712] CONTEXT:  PL/pgSQL function show_partitions(text,text,boolean) line 112 at RAISE
     SQL statement "SELECT partition_tablename                              FROM partman.show_partitions(v_row.parent_table, p_include_default := true) LIMIT 1"
     PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 147 at SQL statement
2023-10-23 12:18:28.502 UTC [2712] DEBUG:  run_maint: v_partition_expression: created_at
2023-10-23 12:18:28.502 UTC [2712] CONTEXT:  PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 169 at RAISE
2023-10-23 12:18:28.503 UTC [2712] DEBUG:  show_partitions: v_parent_schema: public, v_parent_tablename: my_table, v_datetime_string: YYYYMMDD_HH24MISS
2023-10-23 12:18:28.503 UTC [2712] CONTEXT:  PL/pgSQL function show_partitions(text,text,boolean) line 53 at RAISE
     SQL statement "SELECT partition_tablename                       FROM partman.show_partitions(v_row.parent_table, 'DESC') LIMIT 1"
     PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 171 at SQL statement
2023-10-23 12:18:28.503 UTC [2712] DEBUG:  show_partitions: v_sql: SELECT n.nspname::text AS partition_schemaname
             , c.relname::text AS partition_name
             FROM pg_catalog.pg_inherits h
             JOIN pg_catalog.pg_class c ON c.oid = h.inhrelid
             JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
             WHERE h.inhparent = 'public.my_table'::regclass
         AND pg_get_expr(relpartbound, c.oid) != 'DEFAULT' 
             ORDER BY (regexp_match(pg_get_expr(c.relpartbound, c.oid, true), $REGEX$\(([^)]+)\) TO \(([^)]+)\)$REGEX$))[1]::text::timestamptz DESC 
2023-10-23 12:18:28.503 UTC [2712] CONTEXT:  PL/pgSQL function show_partitions(text,text,boolean) line 112 at RAISE
     SQL statement "SELECT partition_tablename                       FROM partman.show_partitions(v_row.parent_table, 'DESC') LIMIT 1"
     PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 171 at SQL statement
2023-10-23 12:18:28.504 UTC [2712] DEBUG:  run_maint: parent_table: public.my_table, v_last_partition: my_table_p20231023_121900
2023-10-23 12:18:28.504 UTC [2712] CONTEXT:  PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 172 at RAISE
2023-10-23 12:18:28.506 UTC [2712] DEBUG:  show_partition_info: v_child_schema: public, v_child_tablename: my_table_p20231023_121900
2023-10-23 12:18:28.506 UTC [2712] CONTEXT:  PL/pgSQL function show_partition_info(text,text,text) line 61 at RAISE
     SQL statement "SELECT child_start_time                                             FROM partman.show_partition_info(v_parent_schema||'.'||v_last_partition, v_row.partition_interval, v_row.parent_table)"
     PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 183 at SQL statement
2023-10-23 12:18:28.506 UTC [2712] DEBUG:  show_partitions: v_parent_schema: public, v_parent_tablename: my_table, v_datetime_string: YYYYMMDD_HH24MISS
2023-10-23 12:18:28.506 UTC [2712] CONTEXT:  PL/pgSQL function show_partitions(text,text,boolean) line 53 at RAISE
     PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 188 at FOR over SELECT rows
2023-10-23 12:18:28.506 UTC [2712] DEBUG:  show_partitions: v_sql: SELECT n.nspname::text AS partition_schemaname
             , c.relname::text AS partition_name
             FROM pg_catalog.pg_inherits h
             JOIN pg_catalog.pg_class c ON c.oid = h.inhrelid
             JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
             WHERE h.inhparent = 'public.my_table'::regclass
         AND pg_get_expr(relpartbound, c.oid) != 'DEFAULT' 
             ORDER BY (regexp_match(pg_get_expr(c.relpartbound, c.oid, true), $REGEX$\(([^)]+)\) TO \(([^)]+)\)$REGEX$))[1]::text::timestamptz DESC 
2023-10-23 12:18:28.506 UTC [2712] CONTEXT:  PL/pgSQL function show_partitions(text,text,boolean) line 112 at RAISE
     PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 188 at FOR over SELECT rows
2023-10-23 12:18:28.511 UTC [2712] DEBUG:  run_maint: v_current_partition_timestamp: <NULL>, v_max_time_default: <NULL>
2023-10-23 12:18:28.511 UTC [2712] CONTEXT:  PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 217 at RAISE

I've looked at this previous discussion, but while I am running the maintenance manually, its unclear if its saying I literally need to INSERT data to trigger new partitions to be created. Is there something I'm missing to trigger the creation of new partitions?

[keithf4]

You either need data in the partition set for partman to make new partitions, or if it is time-based, you can set infinite_time_partitions in the part_config table and it will then create new partitions based on "now" vs the data in the partition set.
For integer/id based partitioning, it is only based on existing data. I have time-based partitioning acting the same as id-based by default for consistency.

[keithf4]

If you expect large gaps in the data insertion rate, you can also set the premake to the expected time window of data so it stays ahead vs setting infinite_time_partitions. There is no harm setting premake to a higher value to cover a larger time period and avoids endlessly creating possibly unnecessary child tables.

[mbrancato]

I added the following to the steps originally taken and then ran run_maintenance() and it had no impact on partitions created. The premake value is currently 4, but it doesn't appear to make any new partitions on subsequent runs of run_maintenance().

UPDATE partman.part_config SET retention = '5 hours', retention_keep_table = false, infinite_time_partitions = true WHERE parent_table = 'public.my_table';

So I tried to redo it with minutes and test live what happens.
On the initial run, it creates a lot of old partitions

initial run

partitions_test=# SELECT partman.create_parent(
partitions_test(#     p_parent_table := 'public.my_table'
partitions_test(#     , p_control := 'created_at'
partitions_test(#     , p_interval := '1 minute'
partitions_test(#     , p_start_partition := date_trunc('minute', NOW())::text
partitions_test(#     , p_default_table := false
partitions_test(# );
 create_parent 
---------------
 t
(1 row)

partitions_test=# UPDATE partman.part_config SET retention = '5 minutes', retention_keep_table = false, infinite_time_partitions = true WHERE parent_table = 'public.my_table';
UPDATE 1
partitions_test=# 
partitions_test=# \d+ my_table
                                             Partitioned table "public.my_table"
   Column   |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 id         | uuid                     |           | not null |         | plain   |             |              | 
 col1       | integer                  |           | not null |         | plain   |             |              | 
 created_at | timestamp with time zone |           | not null | now()   | plain   |             |              | 
Partition key: RANGE (created_at)
Partitions: my_table_p20231023_140000 FOR VALUES FROM ('2023-10-23 14:00:00+00') TO ('2023-10-23 14:01:00+00'),
            my_table_p20231023_140100 FOR VALUES FROM ('2023-10-23 14:01:00+00') TO ('2023-10-23 14:02:00+00'),
            my_table_p20231023_140200 FOR VALUES FROM ('2023-10-23 14:02:00+00') TO ('2023-10-23 14:03:00+00'),
            my_table_p20231023_140300 FOR VALUES FROM ('2023-10-23 14:03:00+00') TO ('2023-10-23 14:04:00+00'),
            my_table_p20231023_140400 FOR VALUES FROM ('2023-10-23 14:04:00+00') TO ('2023-10-23 14:05:00+00'),
            my_table_p20231023_140500 FOR VALUES FROM ('2023-10-23 14:05:00+00') TO ('2023-10-23 14:06:00+00'),
            my_table_p20231023_140600 FOR VALUES FROM ('2023-10-23 14:06:00+00') TO ('2023-10-23 14:07:00+00'),
            my_table_p20231023_140700 FOR VALUES FROM ('2023-10-23 14:07:00+00') TO ('2023-10-23 14:08:00+00'),
            my_table_p20231023_140800 FOR VALUES FROM ('2023-10-23 14:08:00+00') TO ('2023-10-23 14:09:00+00'),
            my_table_p20231023_140900 FOR VALUES FROM ('2023-10-23 14:09:00+00') TO ('2023-10-23 14:10:00+00'),
            my_table_p20231023_141000 FOR VALUES FROM ('2023-10-23 14:10:00+00') TO ('2023-10-23 14:11:00+00'),
            my_table_p20231023_141100 FOR VALUES FROM ('2023-10-23 14:11:00+00') TO ('2023-10-23 14:12:00+00'),
            my_table_p20231023_141200 FOR VALUES FROM ('2023-10-23 14:12:00+00') TO ('2023-10-23 14:13:00+00'),
            my_table_p20231023_141300 FOR VALUES FROM ('2023-10-23 14:13:00+00') TO ('2023-10-23 14:14:00+00'),
            my_table_p20231023_141400 FOR VALUES FROM ('2023-10-23 14:14:00+00') TO ('2023-10-23 14:15:00+00'),
            my_table_p20231023_141500 FOR VALUES FROM ('2023-10-23 14:15:00+00') TO ('2023-10-23 14:16:00+00'),
            my_table_p20231023_141600 FOR VALUES FROM ('2023-10-23 14:16:00+00') TO ('2023-10-23 14:17:00+00'),
            my_table_p20231023_141700 FOR VALUES FROM ('2023-10-23 14:17:00+00') TO ('2023-10-23 14:18:00+00'),
            my_table_p20231023_141800 FOR VALUES FROM ('2023-10-23 14:18:00+00') TO ('2023-10-23 14:19:00+00'),
            my_table_p20231023_141900 FOR VALUES FROM ('2023-10-23 14:19:00+00') TO ('2023-10-23 14:20:00+00'),
            my_table_p20231023_142000 FOR VALUES FROM ('2023-10-23 14:20:00+00') TO ('2023-10-23 14:21:00+00'),
            my_table_p20231023_142100 FOR VALUES FROM ('2023-10-23 14:21:00+00') TO ('2023-10-23 14:22:00+00'),
            my_table_p20231023_142200 FOR VALUES FROM ('2023-10-23 14:22:00+00') TO ('2023-10-23 14:23:00+00'),
            my_table_p20231023_142300 FOR VALUES FROM ('2023-10-23 14:23:00+00') TO ('2023-10-23 14:24:00+00'),
            my_table_p20231023_142400 FOR VALUES FROM ('2023-10-23 14:24:00+00') TO ('2023-10-23 14:25:00+00'),
            my_table_p20231023_142500 FOR VALUES FROM ('2023-10-23 14:25:00+00') TO ('2023-10-23 14:26:00+00'),
            my_table_p20231023_142600 FOR VALUES FROM ('2023-10-23 14:26:00+00') TO ('2023-10-23 14:27:00+00'),
            my_table_p20231023_142700 FOR VALUES FROM ('2023-10-23 14:27:00+00') TO ('2023-10-23 14:28:00+00'),
            my_table_p20231023_142800 FOR VALUES FROM ('2023-10-23 14:28:00+00') TO ('2023-10-23 14:29:00+00'),
            my_table_p20231023_142900 FOR VALUES FROM ('2023-10-23 14:29:00+00') TO ('2023-10-23 14:30:00+00'),
            my_table_p20231023_143000 FOR VALUES FROM ('2023-10-23 14:30:00+00') TO ('2023-10-23 14:31:00+00'),
            my_table_p20231023_143100 FOR VALUES FROM ('2023-10-23 14:31:00+00') TO ('2023-10-23 14:32:00+00'),
            my_table_p20231023_143200 FOR VALUES FROM ('2023-10-23 14:32:00+00') TO ('2023-10-23 14:33:00+00'),
            my_table_p20231023_143300 FOR VALUES FROM ('2023-10-23 14:33:00+00') TO ('2023-10-23 14:34:00+00'),
            my_table_p20231023_143400 FOR VALUES FROM ('2023-10-23 14:34:00+00') TO ('2023-10-23 14:35:00+00'),
            my_table_p20231023_143500 FOR VALUES FROM ('2023-10-23 14:35:00+00') TO ('2023-10-23 14:36:00+00')

On subsequent runs, it deleted partitions to match retention.

subsequent run

partitions_test=# SELECT partman.run_maintenance();                                                                                                                                                             run_maintenance 
-----------------
 
(1 row)

partitions_test=# \d+ my_table
                                             Partitioned table "public.my_table"
   Column   |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 id         | uuid                     |           | not null |         | plain   |             |              | 
 col1       | integer                  |           | not null |         | plain   |             |              | 
 created_at | timestamp with time zone |           | not null | now()   | plain   |             |              | 
Partition key: RANGE (created_at)
Partitions: my_table_p20231023_142600 FOR VALUES FROM ('2023-10-23 14:26:00+00') TO ('2023-10-23 14:27:00+00'),
            my_table_p20231023_142700 FOR VALUES FROM ('2023-10-23 14:27:00+00') TO ('2023-10-23 14:28:00+00'),
            my_table_p20231023_142800 FOR VALUES FROM ('2023-10-23 14:28:00+00') TO ('2023-10-23 14:29:00+00'),
            my_table_p20231023_142900 FOR VALUES FROM ('2023-10-23 14:29:00+00') TO ('2023-10-23 14:30:00+00'),
            my_table_p20231023_143000 FOR VALUES FROM ('2023-10-23 14:30:00+00') TO ('2023-10-23 14:31:00+00'),
            my_table_p20231023_143100 FOR VALUES FROM ('2023-10-23 14:31:00+00') TO ('2023-10-23 14:32:00+00'),
            my_table_p20231023_143200 FOR VALUES FROM ('2023-10-23 14:32:00+00') TO ('2023-10-23 14:33:00+00'),
            my_table_p20231023_143300 FOR VALUES FROM ('2023-10-23 14:33:00+00') TO ('2023-10-23 14:34:00+00'),
            my_table_p20231023_143400 FOR VALUES FROM ('2023-10-23 14:34:00+00') TO ('2023-10-23 14:35:00+00'),
            my_table_p20231023_143500 FOR VALUES FROM ('2023-10-23 14:35:00+00') TO ('2023-10-23 14:36:00+00')

It never creates new tables, and eventually drops all child tables until it has only one old child table left:

partitions_test=# SELECT partman.run_maintenance();
 run_maintenance 
-----------------
 
(1 row)

partitions_test=# \d+ my_table
                                             Partitioned table "public.my_table"
   Column   |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 id         | uuid                     |           | not null |         | plain   |             |              | 
 col1       | integer                  |           | not null |         | plain   |             |              | 
 created_at | timestamp with time zone |           | not null | now()   | plain   |             |              | 
Partition key: RANGE (created_at)
Partitions: my_table_p20231023_143400 FOR VALUES FROM ('2023-10-23 14:34:00+00') TO ('2023-10-23 14:35:00+00'),
            my_table_p20231023_143500 FOR VALUES FROM ('2023-10-23 14:35:00+00') TO ('2023-10-23 14:36:00+00')

partitions_test=# SELECT partman.run_maintenance();
WARNING:  Attempt to drop final partition in partition set public.my_table as part of retention policy. If you see this message multiple times for the same table, advise reviewing retention policy and/or data entry into the partition set. Also consider setting "infinite_time_partitions = true" if there are large gaps in data insertion.).
 run_maintenance 
-----------------
 
(1 row)

partitions_test=# \d+ my_table
                                             Partitioned table "public.my_table"
   Column   |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 id         | uuid                     |           | not null |         | plain   |             |              | 
 col1       | integer                  |           | not null |         | plain   |             |              | 
 created_at | timestamp with time zone |           | not null | now()   | plain   |             |              | 
Partition key: RANGE (created_at)
Partitions: my_table_p20231023_143500 FOR VALUES FROM ('2023-10-23 14:35:00+00') TO ('2023-10-23 14:36:00+00')

[mbrancato]

Hi @keithf4
I think I've figured out the nuance of this. Even with infinite_time_partitions = true it will not create new partitions without data in some partition. When there is new data in some partition, it then makes partitions for all partitions up till now() + premake.

Ultimately, I just always wanted there to be a fixed number of partitions. For example, 48 partitions, one for each hour of retention regardless if there was data in them.

[keithf4]

Hmm... I'll have to look into that. I can see this being a desired pattern, so I'll look into it to see if I can get the infinite flag to work even if there is no data at all. I had assumed that would work like that.

[mbrancato]

@keithf4 would you like me to create an issue from this to support no data new partition creation?

[keithf4]

Sure. Thank you