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

Hello

Thanks for including 0001 here!

I reviewed the latest v15 version, and I think overall there are a few
leftover issues:

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;

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;

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;

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';