From: Chao Li <li.evan.chao@gmail.com>
Message-Id: <54DABC65-787E-4DA9-895C-140A3CF862CF@gmail.com>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_C2609B80-795B-4F9F-AC2B-D81A2E6FF9A7"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.700.51.1.1\))
Subject: Re: REPACK (CONCURRENTLY) fails when replica identity index is
 dropped
Date: Fri, 28 Aug 2026 14:04:15 +0800
In-Reply-To: 
 <CAEze2WhT2=bm8s45MjQf+YJ5Md7TTXy=8MKKOYsWspHGXxNWKA@mail.gmail.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>,
 pgsql-hackers@postgresql.org,
 alvherre@kurilemu.de
To: Matthias van de Meent <boekewurm+postgres@gmail.com>
References: <apCBRKCH8jwKiaSY@nathan>
 <CAEze2WhT2=bm8s45MjQf+YJ5Md7TTXy=8MKKOYsWspHGXxNWKA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/54DABC65-787E-4DA9-895C-140A3CF862CF%40gmail.com>
Precedence: bulk


--Apple-Mail=_C2609B80-795B-4F9F-AC2B-D81A2E6FF9A7
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8


> On Aug 28, 2026, at 03:31, Matthias van de Meent =
<boekewurm+postgres@gmail.com> wrote:
>=20
> On Thu, 27 Aug 2026 at 20:26, Nathan Bossart =
<nathandbossart@gmail.com> wrote:
>>=20
>> I don't fully understand the mechanics of this one, but here is a
>> reproducer:
>>=20
>>    CREATE TABLE t (a INT PRIMARY KEY, b INT, c TEXT);
>>    INSERT INTO t SELECT g, g, repeat('x', 1000) FROM =
generate_series(1, 1000000) g;
>>    CREATE UNIQUE INDEX i ON t (a);
>>    ALTER TABLE t REPLICA IDENTITY USING INDEX i;
>>    DROP INDEX i;
>>    REPACK (CONCURRENTLY) t;
>>=20
>>    -- in a separate session, while REPACK is still running
>>    DELETE FROM t WHERE a =3D 1;
>>=20
>> This produces the following ERROR from the REPACK command:
>>=20
>>    ERROR:  incomplete delete info
>>    CONTEXT:  slot "pg_repack_34213", output plugin "pgrepack", in the =
change callback, associated LSN 0/A6CC1ED0
>>    REPACK decoding worker
>>=20
>> I think this can be addressed by verifying the index exists in
>> check_concurrent_repack_requirements() and erroring out if it =
doesn't.
>=20
> I think the issue is caused by the following: REPACK's check has the
> incorrect assumption that REPLICA IDENTITY USING INDEX either reverts
> to DEFAULT or falls back to the behaviour of DEFAULT if the identity
> index gets dropped, and thus uses GetRelationIdentityOrPK(), which
> hides a lack of replica identity index.  The issue shows up due to the
> following garden path of data flows:
>=20
> 1. A table with REPLICA IDENTITY USING INDEX doesn't fall back to
> REPLICA IDENTITY DEFAULT once the identity index is dropped.
> 2. In the catcache, the table won't fall back to rd_replidindex =3D
> pkeyIndex when replident=3D'i', but instead will set
> rd_replidindex=3DInvalidOid.
> See the tail end of RelationGetIndexList.
> 3. Then, in heap_delete, it calls ExtractReplicaIdentity() to find the
> key of the deleted tuple.
> 3a. ExtractR_I_() checks the identity key attributes from
> RelationGetIndexAttrBitmap(..., INDEX_ATTR_BITMAP_IDENTITY_KEY), which
> also only uses rd_replidindex, and doesn't fall back to the primary
> key index's attributes.
> 3b. If ExtractR_I_() doesn't have identity key attributes, it returns =
NULL
> 3c. heap_delete thus doesn't have any logical identity attributes to
> log, and treats the delete operation as any non-logical deletion when
> logging the data.
> 4. Finally, the DELETE record gets decoded, and the logical plugin
> finds out that no logical key data was included, and promptly ERRORs
> out.
>=20
> The attached patch is a blind shot that I suspect will fix the issue.
>=20
>=20
> Kind regards,
>=20
> Matthias van de Meent
> Databricks (https://www.databricks.com)
> <v1-0001-Repack-Fix-replica-identity-index-check.patch>

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=E2=80=99t 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=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.
```

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/


--Apple-Mail=_C2609B80-795B-4F9F-AC2B-D81A2E6FF9A7
Content-Disposition: attachment;
	filename=block_drop_index.diff
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="block_drop_index.diff"
Content-Transfer-Encoding: 7bit

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index fd144d783d9..ec3b374f6fa 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1685,6 +1685,17 @@ RemoveRelations(DropStmt *drop)
 			continue;
 		}
 
+		/*
+		 * An explicitly selected replica identity must remain available until
+		 * the table's replica identity is changed.
+		 */
+		if (drop->removeType == OBJECT_INDEX && get_index_isreplident(relOid))
+			ereport(ERROR,
+					errcode(ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST),
+					errmsg("cannot drop index \"%s\" because it is used as replica identity",
+						   rel->relname),
+					errhint("Use ALTER TABLE ... REPLICA IDENTITY to change the table's replica identity first."));
+
 		/*
 		 * Decide if concurrent mode needs to be used here or not.  The
 		 * callback retrieved the rel's persistence for us.
diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out
index 1560cd04125..bcfcf1a1b4b 100644
--- a/src/test/regress/expected/replica_identity.out
+++ b/src/test/regress/expected/replica_identity.out
@@ -133,6 +133,10 @@ SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass
      1
 (1 row)
 
+-- An explicitly selected replica identity index cannot be dropped.
+DROP INDEX test_replica_identity_keyab_key;
+ERROR:  cannot drop index "test_replica_identity_keyab_key" because it is used as replica identity
+HINT:  Use ALTER TABLE ... REPLICA IDENTITY to change the table's replica identity first.
 ----
 -- Make sure non index cases work
 ----
@@ -143,6 +147,7 @@ SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
  d
 (1 row)
 
+DROP INDEX test_replica_identity_keyab_key;
 SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident;
  count 
 -------
@@ -169,7 +174,6 @@ Indexes:
     "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3))
     "test_replica_identity_hash" hash (nonkey)
     "test_replica_identity_keyab" btree (keya, keyb)
-    "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb)
     "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey)
     "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text
     "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE
@@ -200,7 +204,6 @@ Indexes:
     "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3))
     "test_replica_identity_hash" hash (nonkey)
     "test_replica_identity_keyab" btree (keya, keyb)
-    "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb)
     "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey)
     "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text
     "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE
diff --git a/src/test/regress/sql/replica_identity.sql b/src/test/regress/sql/replica_identity.sql
index 4ebb097f282..b8d29ca8fa5 100644
--- a/src/test/regress/sql/replica_identity.sql
+++ b/src/test/regress/sql/replica_identity.sql
@@ -65,11 +65,15 @@ SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
 \d test_replica_identity
 SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident;
 
+-- An explicitly selected replica identity index cannot be dropped.
+DROP INDEX test_replica_identity_keyab_key;
+
 ----
 -- Make sure non index cases work
 ----
 ALTER TABLE test_replica_identity REPLICA IDENTITY DEFAULT;
 SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
+DROP INDEX test_replica_identity_keyab_key;
 SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident;
 
 ALTER TABLE test_replica_identity REPLICA IDENTITY FULL;

--Apple-Mail=_C2609B80-795B-4F9F-AC2B-D81A2E6FF9A7--