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

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
...

Reproduced in Postgresql 15.1 too.

Sergey Belyashov

пт, 20 янв. 2023 г. в 18:42, Tom Lane <tgl@sss.pgh.pa.us>:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > Some database have a partitioned table with unique index used as REPLICA
> > IDENTITY. pg_dump places ALTER TABLE tbl REPLICA IDENTITY USING INDEX
> > some_idx after partial index creation. But Postgresql fails to restore such
> > dump because replica identity cannot be set on invalid index some_idx:
> > partition indices are not created.
>
> Please provide a concrete example, preferably a SQL script to create
> a database that triggers the problem.  There are enough variables
> here that nobody is likely to be excited about trying to reverse-
> engineer a test case from only this amount of detail.
>
>                         regards, tom lane