Sergey Belyashov <sergey.belyashov@gmail.com> writes:
> SQL:
> create database testdb;
> \c testdb
> create table tbl (id integer not null primary key) partition by list (id);
> create table tbl_1 partition of tbl for values in (1);
> alter table tbl replica identity using index tbl_pkey;
> Next do:
> $ pg_dump testdb >testdb.sql
> $ psql testdb -c "drop table tbl"
> $ psql testdb <testdb.sql
> result:
> ...
> ALTER TABLE
> ERROR: cannot use invalid index "tbl_pkey" as replica identity
Thanks for the test case. So the problem occurs because pg_dump dumps
the commands in this order:
ALTER TABLE ONLY public.tbl
ADD CONSTRAINT tbl_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.tbl REPLICA IDENTITY USING INDEX tbl_pkey;
ALTER TABLE ONLY public.tbl_1
ADD CONSTRAINT tbl_1_pkey PRIMARY KEY (id);
ALTER INDEX public.tbl_pkey ATTACH PARTITION public.tbl_1_pkey;
but the backend won't take the ALTER REPLICA IDENTITY command
until after the ATTACH PARTITION.
We could probably make pg_dump emit things in the order that works,
but it'd be a significant amount of extra complication there
(the ALTER REPLICA IDENTITY command couldn't be treated as just
part of the index definition).
I wonder why it is that the backend rejects this sequence.
I see that you can do this:
regression=# create table tbl (id integer not null primary key) partition by list (id);
CREATE TABLE
regression=# alter table tbl replica identity using index tbl_pkey;
ALTER TABLE
and it doesn't seem like the partitioned index is notably more
valid in this state than in the one that pg_dump has created.
So I think it might be better to fix the backend to allow this
sequence of operations.
regards, tom lane