From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sergey Belyashov <sergey.belyashov@gmail.com>
cc: pgsql-bugs@lists.postgresql.org, Alvaro Herrera <alvherre@alvh.no-ip.org>
Subject: Re: BUG #17756: Invalid replica indentity set order in a dump
In-reply-to: 
 <CAOe0RDwjMKc2nn-VRePsaNR0zhe9e0tYQwU3JNWgic4DCw=Jrw@mail.gmail.com>
References: <17756-dd50e8e0c8dd4a40@postgresql.org>
 <429827.1674229330@sss.pgh.pa.us>
 <CAOe0RDwjMKc2nn-VRePsaNR0zhe9e0tYQwU3JNWgic4DCw=Jrw@mail.gmail.com>
Comments: In-reply-to Sergey Belyashov <sergey.belyashov@gmail.com>
	message dated "Fri, 20 Jan 2023 20:45:57 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <538203.1674238441.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 20 Jan 2023 13:14:01 -0500
Message-ID: <538204.1674238441@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/538204.1674238441%40sss.pgh.pa.us>
Precedence: bulk

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=3D# create table tbl (id integer not null primary key) partitio=
n by list (id);
CREATE TABLE
regression=3D# 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