repacking partitioned table indexes failed with: Could not find index/cannot create index on partitioned table concurrently

[JohnHien]

I used pg_repack 1.5.0 to repack the indexes of parent table and its children on both PG11 and PG14, and everything worked as expected:

CREATE TABLE parent(val INTEGER PRIMARY KEY);
CREATE TABLE child_1(val INTEGER PRIMARY KEY) INHERITS(parent);
CREATE TABLE child_2(val INTEGER PRIMARY KEY) INHERITS(parent);

pg_repack --dbname=postgres --parent-table=parent --only-indexes
INFO: repacking indexes of "public.child_1"
INFO: repacking index "public.child_1_pkey"
INFO: repacking indexes of "public.child_2"
INFO: repacking index "public.child_2_pkey"
INFO: repacking indexes of "public.parent"
INFO: repacking index "public.parent_pkey"

However, when I used the same pg_repack command on a partitioned table, I encountered errors. The definition of the partitioned table is as follows:

CREATE TABLE partitioned (val INT PRIMARY KEY) PARTITION BY RANGE (val);
CREATE TABLE partition_1 PARTITION OF partitioned FOR VALUES FROM (0) TO (1000);
CREATE TABLE partition_2 PARTITION OF partitioned FOR VALUES FROM (1000) TO (2000);
CREATE TABLE partition_default PARTITION OF partitioned DEFAULT;

On PG14, the repack_index_swap function could not find the new index index_16766 during rebuilding the index on the partitioned table because it only looks for indexes whose relkind is 'i', while the index on partitioned table has relkind 'I', so the index could not be found.

pg_repack --dbname=postgres --parent-table=partitioned --only-indexes
INFO: repacking indexes of "public.partition_1"
INFO: repacking index "public.partition_1_pkey"
INFO: repacking indexes of "public.partition_2"
INFO: repacking index "public.partition_2_pkey"
INFO: repacking indexes of "public.partition_default"
INFO: repacking index "public.partition_default_pkey"
INFO: repacking indexes of "public.partitioned"
INFO: repacking index "public.partitioned_pkey"
ERROR: query failed: ERROR:  Could not find index 'index_16766', found 0 matches
DETAIL: query was: SELECT repack.repack_index_swap($1)

On PG11, the indexes on partitions were successfully rebuilt. But rebuilding the index on the partitioned table failed with the error: cannot create index on partitioned table concurrently since PG11 does not support concurrent index creation on partitioned tables.

pg_repack --dbname=postgres --parent-table=partitioned --only-indexes
INFO: repacking indexes of "public.partition_1"
INFO: repacking index "public.partition_1_pkey"
INFO: repacking indexes of "public.partition_2"
INFO: repacking index "public.partition_2_pkey"
INFO: repacking indexes of "public.partition_default"
INFO: repacking index "public.partition_default_pkey"
INFO: repacking indexes of "public.partitioned"
INFO: repacking index "public.partitioned_pkey"
WARNING: Error creating index "public"."index_16573": ERROR:  cannot create index on partitioned table "partitioned" concurrently
WARNING: Skipping index swapping for "public.partitioned", since no new indexes built
INFO: Skipping drop of index_16573
WARNING: repack failed for "public.partitioned"

I noticed that the regression tests for pg_repack include only inherited tables cases and no partitioned tables cases. Is it because pg_repack does not support partitioned tables?

[Melkij]

Well, partially supported. We really don't have built-in tests for declarative partitioning since it is impossible to ensure their execution on older but still supported versions of postgresql. We currently support builds for 9.5 and 9.6. Perhaps it’s time to forget about them if someone writes a PR with declarative partitioning tests.

PS: I treat --only-indexes like sort of a deprecated option for old databases; it is better to use native reindex concurrently in postgresql 12+

[andreasscherbaum]

We currently support builds for 9.5 and 9.6.

Or we can exclude the tests for the older versions, because we know it's not a supported feature.

[za-arthur]

I could reproduce only the second case when pg_repack returns error cannot create index on partitioned table concurrently.

cannot create index on partitioned table concurrently since PG11 does not support concurrent index creation on partitioned tables.

AFAIK Postgres 16 doesn't support concurrent index creation on partitioned tables neither: https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

Concurrent builds for indexes on partitioned tables are currently not supported. However, you may concurrently build the index on each partition individually and then finally create the partitioned index non-concurrently in order to reduce the time where writes to the partitioned table will be locked out. In this case, building the partitioned index is a metadata only operation.

[za-arthur]

This issues should be fixed by https://github.com/reorg/pg_repack/pull/398.

[za-arthur]

The PR https://github.com/reorg/pg_repack/pull/398 was merged. @ZhangHien please feel free to check it.