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(at)sss(dot)pgh(dot)pa(dot)us>:
>
> PG Bug reporting form <noreply(at)postgresql(dot)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