Re: tablecmds: fix bug where index rebuild loses replica identity on partitions

> On Aug 27, 2026, at 07:21, Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
>
> Hello
>
> Thanks for including 0001 here!
>
> I reviewed the latest v15 version, and I think overall there are a few
> leftover issues:

Thanks for the review.

>
> 1. leaf tablespace is still lost when default_tablespace is set
>
> CREATE TABLESPACE ts LOCATION '/path/to/empty/dir';
> CREATE TABLE t (id int, val int) PARTITION BY RANGE (id);
> CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (0) TO (100);
> CREATE INDEX t_idx ON t (val) TABLESPACE ts;
> ALTER INDEX t1_val_idx SET TABLESPACE pg_default; -- leaf
> deliberately elsewhere
>
> SET default_tablespace = 'ts';
> ALTER TABLE t ALTER COLUMN val TYPE bigint;
> RESET default_tablespace;
>
> SELECT c.relname, coalesce(s.spcname,'<db default>') AS tablespace
> FROM pg_class c LEFT JOIN pg_tablespace s ON s.oid = c.reltablespace
> WHERE c.relname IN ('t_idx','t1_val_idx') ORDER BY 1;

Confirmed and fixed in v15.

>
> 2. intermediate partitioned indexes are still stripped
>
> CREATE TABLE root (id int, val int) PARTITION BY RANGE (id);
> CREATE TABLE mid (id int, val int) PARTITION BY RANGE (id);
> CREATE TABLE leaf PARTITION OF mid FOR VALUES FROM (0) TO (50);
> ALTER TABLE root ATTACH PARTITION mid FOR VALUES FROM (0) TO (100);
> CREATE INDEX root_idx ON root ((val + 1));
>
> ALTER INDEX mid_val_1_idx RENAME TO mid_custom;
> ALTER INDEX leaf_val_1_idx RENAME TO leaf_custom;
> COMMENT ON INDEX mid_custom IS 'mid comment';
> COMMENT ON INDEX leaf_custom IS 'leaf comment';
> ALTER INDEX mid_custom ALTER COLUMN 1 SET STATISTICS 777;
> ALTER INDEX leaf_custom ALTER COLUMN 1 SET STATISTICS 888;
>
> SELECT c.relname, obj_description(c.oid,'pg_class') AS comment,
> (SELECT attstattarget FROM pg_attribute a
> WHERE a.attrelid = c.oid AND a.attnum = 1) AS stattarget
> FROM pg_class c WHERE c.relkind IN ('i','I') AND c.relname !~ '^pg_'
> ORDER BY 1;
> ALTER TABLE root ALTER COLUMN val TYPE bigint;
> SELECT c.relname, obj_description(c.oid,'pg_class') AS comment,
> (SELECT attstattarget FROM pg_attribute a
> WHERE a.attrelid = c.oid AND a.attnum = 1) AS stattarget
> FROM pg_class c WHERE c.relkind IN ('i','I') AND c.relname !~ '^pg_'
> ORDER BY 1;
>

Confirmed and fixed in v15.

> 2b: that includes the replica identity
>
> CREATE TABLE root (id int not null, val int not null) PARTITION BY RANGE (id);
> CREATE TABLE mid (id int not null, val int not null) PARTITION BY RANGE (id);
> CREATE TABLE leaf PARTITION OF mid FOR VALUES FROM (0) TO (50);
> ALTER TABLE root ATTACH PARTITION mid FOR VALUES FROM (0) TO (100);
> CREATE UNIQUE INDEX root_uq ON root (id, val);
> ALTER TABLE mid REPLICA IDENTITY USING INDEX mid_id_val_idx;
>
> SELECT tc.relname AS "table", tc.relreplident, c.relname AS index,
> i.indisreplident
> FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
> JOIN pg_class tc ON tc.oid = i.indrelid
> WHERE tc.relname IN ('root','mid','leaf') ORDER BY 1;
> ALTER TABLE root ALTER COLUMN val TYPE bigint;
> SELECT tc.relname AS "table", tc.relreplident, c.relname AS index,
> i.indisreplident
> FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
> JOIN pg_class tc ON tc.oid = i.indrelid
> WHERE tc.relname IN ('root','mid','leaf') ORDER BY 1;

Confirmed and fixed in v15.

>
> 3: leaf constraint comments are still lost, while comments on its
> indexes are now preserved
>
> CREATE TABLE cc (id int not null, val int not null) PARTITION BY RANGE (id);
> CREATE TABLE cc1 PARTITION OF cc FOR VALUES FROM (0) TO (100);
> ALTER TABLE cc ADD CONSTRAINT cc_pkey PRIMARY KEY (id, val);
> COMMENT ON CONSTRAINT cc1_pkey ON cc1 IS 'leaf constraint comment';
> COMMENT ON INDEX cc1_pkey IS 'leaf index comment';
>
> SELECT obj_description('cc1_pkey'::regclass,'pg_class') AS index_comment,
> obj_description(oid,'pg_constraint') AS constraint_comment
> FROM pg_constraint WHERE conrelid='cc1'::regclass AND contype='p';
> ALTER TABLE cc ALTER COLUMN val TYPE bigint;
> SELECT obj_description('cc1_pkey'::regclass,'pg_class') AS index_comment,
> obj_description(oid,'pg_constraint') AS constraint_comment
> FROM pg_constraint WHERE conrelid='cc1'::regclass AND contype='p';

Confirmed and fixed in v15.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/