Re: REPACK (CONCURRENTLY) fails when replica identity index is dropped

Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> wrote:
> After dropping the index, pg_class.relreplident is still 'i', but the corresponding pg_index entry is deleted, so the table is left in a stale state. If we only check whether the REPLICA IDENTITY index is valid in REPACK, that prevents REPACK from starting, but doesn’t resolve the stale state itself.
>
> We cannot assume the intended replacement replica identity after removing an explicitly selected index. For example, the user might want DEFAULT, FULL, or maybe another index. Should we instead prevent dropping of an index while it is used as REPLICA IDENTITY?
>
> The attached diff makes a change in the direction, like this:
> ```
> evantest=# CREATE TABLE t (a INT PRIMARY KEY, b INT, c TEXT);
> CREATE TABLE
> evantest=# CREATE UNIQUE INDEX i ON t (a);
> CREATE INDEX
> evantest=# ALTER TABLE t REPLICA IDENTITY USING INDEX i;
> ALTER TABLE
> evantest=# DROP INDEX i;
> ERROR: cannot drop index "i" because it is used as replica identity
> HINT: Use ALTER TABLE ... REPLICA IDENTITY to change the table's replica identity first.

I agree that the core issue is that we allow dropping an index that is being
used as replica identity.

Regarding catalog entries already broken this way, it appears that pg_upgrade
fixes them because pg_dump does not issue "ALTER TABLE ... REPLICA IDENTITY
USING INDEX ..." if there is not identity index. Thus after pg_restore,
pg_class(relreplident) becomes REPLICA_IDENTITY_DEFAULT.

--
Antonin Houska
Web: https://www.cybertec-postgresql.com