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

agora inbox for pgsql-hackers@postgresql.org  
help / color / mirror / Atom feed

From: Antonin Houska <ah@cybertec.at>
To: Chao Li <li.evan.chao@gmail.com>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>
Cc: pgsql-hackers@postgresql.org
Cc: alvherre@kurilemu.de
Subject: Re: REPACK (CONCURRENTLY) fails when replica identity index is dropped
Date: Tue, 01 Sep 2026 12:10:44 +0200
Message-ID: <30425.1788257444@localhost> (raw)
In-Reply-To: <54DABC65-787E-4DA9-895C-140A3CF862CF@gmail.com>
References: <apCBRKCH8jwKiaSY@nathan>
	<CAEze2WhT2=bm8s45MjQf+YJ5Md7TTXy=8MKKOYsWspHGXxNWKA@mail.gmail.com>
	<54DABC65-787E-4DA9-895C-140A3CF862CF@gmail.com>

Chao Li <li.evan.chao@gmail.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

view thread (12+ messages)  latest in thread

Message-ID: <30425.1788257444@localhost>
Permalink:  ../30425.1788257444@localhost/
Also on:    postgresql.org/message-id/30425.1788257444@localhost

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: ah@cybertec.at, li.evan.chao@gmail.com, boekewurm+postgres@gmail.com, nathandbossart@gmail.com, alvherre@kurilemu.de
  Subject: Re: REPACK (CONCURRENTLY) fails when replica identity index is dropped
  In-Reply-To: <30425.1788257444@localhost>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox