From: Antonin Houska <ah@cybertec.at>
To: Chao Li <li.evan.chao@gmail.com>
cc: Matthias van de Meent <boekewurm+postgres@gmail.com>,
    Nathan Bossart <nathandbossart@gmail.com>,
    pgsql-hackers@postgresql.org, alvherre@kurilemu.de
Subject: Re: REPACK (CONCURRENTLY) fails when replica identity index is
 dropped
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>
Comments: In-reply-to Chao Li <li.evan.chao@gmail.com>
   message dated "Fri, 28 Aug 2026 14:04:15 +0800."
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Date: Tue, 01 Sep 2026 12:10:44 +0200
Message-ID: <30425.1788257444@localhost>
Archived-At: 
 <https://www.postgresql.org/message-id/30425.1788257444%40localhost>
Precedence: bulk

Chao Li <li.evan.chao@gmail.com> wrote:
> After dropping the index, pg_class.relreplident is still 'i', but the cor=
responding 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, t=
hat prevents REPACK from starting, but doesn=E2=80=99t resolve the stale st=
ate itself.
>=20
> We cannot assume the intended replacement replica identity after removing=
 an explicitly selected index. For example, the user might want DEFAULT, FU=
LL, or maybe another index. Should we instead prevent dropping of an index =
while it is used as REPLICA IDENTITY?
>=20
> The attached diff makes a change in the direction, like this:
> ```
> evantest=3D# CREATE TABLE t (a INT PRIMARY KEY, b INT, c TEXT);
> CREATE TABLE
> evantest=3D# CREATE UNIQUE INDEX i ON t (a);
> CREATE INDEX
> evantest=3D# ALTER TABLE t REPLICA IDENTITY USING INDEX i;
> ALTER TABLE
> evantest=3D# 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_upgra=
de
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.

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