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

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