Re: BUG #17756: Invalid replica indentity set order in a dump

Sergey Belyashov <sergey(dot)belyashov(at)gmail(dot)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