LIST partitioned table does not get new partitions with run_maintenance* calls

[keithf4]

Discussed in #702

^{Originally posted by kmustkivi November 12, 2024}
Hello,

Am I doing something wrong or perhaps I have stumbled upon a bug that affects LIST partitioned tables but here is the POC tested on PG v14.13, pg_partman v5.1.0:

CREATE SCHEMA IF NOT EXISTS partman_test;

CREATE TABLE partman_test.id_listpart_table (
    id      bigint NOT NULL GENERATED ALWAYS AS IDENTITY
  , partkey bigint
  , PRIMARY KEY (partkey, id)
)
PARTITION BY LIST (partkey);

CREATE INDEX ON partman_test.id_listpart_table (partkey);

\d+ partman_test.id_listpart_table

SELECT partman.create_parent(
    p_parent_table := 'partman_test.id_listpart_table'
  , p_control      := 'partkey'
  , p_interval     := '1'
  , p_type         := 'list'
);

\d+ partman_test.id_listpart_table

INSERT INTO partman_test.id_listpart_table (partkey) VALUES (0);
INSERT INTO partman_test.id_listpart_table (partkey) VALUES (1);
INSERT INTO partman_test.id_listpart_table (partkey) VALUES (2);
INSERT INTO partman_test.id_listpart_table (partkey) VALUES (3);
INSERT INTO partman_test.id_listpart_table (partkey) VALUES (4);

CALL partman.run_maintenance_proc();

It seems that the function partman.show_partition_info called within the maintenance subroutines returns NULL values for all items and thus nothing gets done from there on.

[keithf4]

Created an issue for this since it is a valid bug. Thanks for reporting!

[keithf4]

Rather odd because I'm doing something similar in this test and it seems to be working. Will keep investigating

https://github.com/pgpartman/pg_partman/blob/master/test/test-id-1-list.sql

[keithf4]

So I think I found the issue. I ran into this elsewhere in that PG returns bigint values differently than int values in some cases in the partition definition

See your table description:

github702=# \d+ partman_test.id_listpart_table
                                     Partitioned table "partman_test.id_listpart_table"
 Column  |  Type  | Collation | Nullable |           Default            | Storage | Compression | Stats target | Description 
---------+--------+-----------+----------+------------------------------+---------+-------------+--------------+-------------
 id      | bigint |           | not null | generated always as identity | plain   |             |              | 
 partkey | bigint |           | not null |                              | plain   |             |              | 
Partition key: LIST (partkey)
Indexes:
    "id_listpart_table_pkey" PRIMARY KEY, btree (partkey, id)
    "id_listpart_table_partkey_idx" btree (partkey)
Partitions: partman_test.id_listpart_table_p0 FOR VALUES IN ('0'),
            partman_test.id_listpart_table_p1 FOR VALUES IN ('1'),
            partman_test.id_listpart_table_p2 FOR VALUES IN ('2'),
            partman_test.id_listpart_table_p3 FOR VALUES IN ('3'),
            partman_test.id_listpart_table_p4 FOR VALUES IN ('4'),
            partman_test.id_listpart_table_default DEFAULT

vs the one in my test

keith=# \d+ partman_test.id_taptest_table
                                    Partitioned table "partman_test.id_taptest_table"
 Column |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 col1   | bigint                   |           |          |         | plain    |             |              | 
 col2   | integer                  |           | not null |         | plain    |             |              | 
 col3   | timestamp with time zone |           |          | now()   | plain    |             |              | 
 col4   | text                     |           |          |         | extended |             |              | 
Partition key: LIST (col2)
Indexes:
    "id_taptest_table_col2_idx" btree (col2)
Partitions: partman_test.id_taptest_table_p0 FOR VALUES IN (0),
            partman_test.id_taptest_table_p1 FOR VALUES IN (1),
            partman_test.id_taptest_table_p2 FOR VALUES IN (2),
            partman_test.id_taptest_table_p3 FOR VALUES IN (3),
            partman_test.id_taptest_table_p4 FOR VALUES IN (4),
            partman_test.id_taptest_table_default DEFAULT

I have code to handle that in show_partitions, but not in show_partition_info

-- Have to do a trim here because of inconsistency in quoting different integer types. Ex: bigint boundary values are quoted but int values are not

https://github.com/pgpartman/pg_partman/blob/master/sql/functions/show_partitions.sql#L106

[keithf4]

I've pushed a fix for this into the development branch for version 5.2. It wasn't just the show_partition_info function, it was also show_partitions as well wasn't working properly. So thanks for the hint that there was a problem with the show function here.
If you have a chance to test this, let me know if it's working for you.

Thanks again!

[keithf4]

@kmustkivi Tagging you here since I'm actually not sure if it's notifying you after migrating to an actual issue

[kmustkivi]

Hello @keithf4 !

Indeed, I did not get a notification about the updates here. Anyway, I confirm that all my test with pg_partman 5.2.0 in development branch are now successful and the bug is fixed.

Sincere Thank You for this excellent module and all the help!

[keithf4]

Version 5.2.0 has been released with this fix. Thanks again for reporting and testing.

[kmustkivi]

Thank you so much!