From: Chao Li <li.evan.chao@gmail.com>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_A69970C7-4B6D-4E61-9AC1-A45FFC4C10C4"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: repack: fix a bug to reject deferrable primary key fallback for
 concurrent mode
Message-Id: <10DD5E13-B45D-44F1-BE08-C63E00ABCAC0@gmail.com>
Date: Fri, 17 Apr 2026 11:35:14 +0800
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/10DD5E13-B45D-44F1-BE08-C63E00ABCAC0%40gmail.com>
Precedence: bulk


--Apple-Mail=_A69970C7-4B6D-4E61-9AC1-A45FFC4C10C4
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

Hi,

I am continuing to test REPACK, and I found another issue.

In check_concurrent_repack_requirements(), if a table has no replica =
identity index, the code falls back to using the primary key if one =
exists. The problem is that a deferrable primary key cannot be used for =
this purpose. WAL generation does not consider a deferrable primary key =
to be a replica identity, so concurrent mode may not receive enough old =
tuple information to replay concurrent changes.

I tested this with the following procedure.

1 - Create a table
```
create table t (id int,  v text, primary key (id) deferrable initially =
deferred);
insert into t values (1, 'a');
```

2 -  Attach a debugger to session 1's backend process. I used vscode. =
Add a breakpoint at the first process_concurrent_changes() call. This =
blocks the REPACK process and gives session 2 time to issue a DELETE.

3 - In session 1, issue a repack, it will stop at the breakpoint
```
repack (concurrently) t;
```

4 - In session 2
```
delete from t where id=3D1;
```

5 - Detach session 1 from the debugger, so that repack continues and =
tries to re-apply the delete from session 2.

6 - repack fails with:
```
evantest=3D# repack (concurrently) t;
ERROR:  incomplete delete info
CONTEXT:  slot "repack_96468", output plugin "pgrepack", in the change =
callback, associated LSN 0/2A5717F0
REPACK decoding worker
```

The error comes from this code in pgrepack.c:
```
		case REORDER_BUFFER_CHANGE_DELETE:
			{
				HeapTuple	oldtuple;

				oldtuple =3D change->data.tp.oldtuple;

				if (oldtuple =3D=3D NULL)
					elog(ERROR, "incomplete delete =
info");

				repack_store_change(ctx, relation, =
CHANGE_DELETE, oldtuple);
			}
			break;
```

The root cause is that repack.c assumes rel->rd_pkindex is usable as an =
identity index, but logical decoding does not treat a deferrable primary =
key as replica identity. As a result, the decoding worker may not get =
the old tuple needed to re-apply the delete.

To fix the problem, I think we should just not fall back to deferrable =
primary key in the first place. See the attached patch.

With this patch, repack will quickly for the test:
```
evantest=3D# repack (concurrently) t;
ERROR:  cannot process relation "t"
HINT:  Relation "t" has a deferrable primary key.
```

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


--Apple-Mail=_A69970C7-4B6D-4E61-9AC1-A45FFC4C10C4
Content-Disposition: attachment;
	filename=v1-0001-Reject-deferrable-primary-key-fallback-in-REPACK-.patch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="v1-0001-Reject-deferrable-primary-key-fallback-in-REPACK-.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=20d67bb43755896909663daa62fb0ae19b32b9d75e=20Mon=20Sep=2017=20=
00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20<lic@highgo.com>=0A=
Date:=20Fri,=2017=20Apr=202026=2011:12:36=20+0800=0ASubject:=20[PATCH=20=
v1]=20Reject=20deferrable=20primary=20key=20fallback=20in=20REPACK=0A=20=
CONCURRENTLY=0A=0AREPACK=20CONCURRENTLY=20uses=20logical=20decoding=20to=20=
collect=20concurrent=0Achanges=20and=20then=20replays=20them=20on=20the=20=
new=20heap.=20To=20locate=20rows=20for=0AUPDATE=20and=20DELETE=20replay,=20=
it=20requires=20an=20identity=20index.=0A=0AWhen=20=
RelationGetReplicaIndex()=20returned=20InvalidOid,=20the=20code=20fell=0A=
back=20to=20rel->rd_pkindex=20if=20a=20primary=20key=20existed.=20That=20=
is=20not=20safe=20for=0Adeferrable=20primary=20keys.=20Such=20indexes=20=
are=20not=20considered=20replica=0Aidentity=20indexes=20by=20WAL=20=
generation,=20so=20logical=20decoding=20may=20not=20provide=0Athe=20old=20=
tuple=20needed=20by=20the=20repack=20output=20plugin.=0A=0AThis=20can=20=
make=20replay=20fail=20later=20with=20errors=20such=20as=20"incomplete=20=
delete=0Ainfo"=20from=20the=20decoding=20worker.=0A=0APrevent=20the=20=
fallback=20when=20the=20primary=20key=20is=20deferrable,=20and=20report=20=
the=0Aproblem=20during=20the=20initial=20REPACK=20CONCURRENTLY=20checks=20=
instead.=0A=0AAuthor:=20Chao=20Li=20<lic@highgo.com>=0AReviewed-by:=0A=
Discussion:=20https://postgr.es/m/=0A---=0A=20=
src/backend/commands/repack.c=20|=2014=20++++++++++++++=0A=201=20file=20=
changed,=2014=20insertions(+)=0A=0Adiff=20--git=20=
a/src/backend/commands/repack.c=20b/src/backend/commands/repack.c=0A=
index=2067364cc60e3..2c89cd17db4=20100644=0A---=20=
a/src/backend/commands/repack.c=0A+++=20b/src/backend/commands/repack.c=0A=
@@=20-926,7=20+926,21=20@@=20=
check_concurrent_repack_requirements(Relation=20rel,=20Oid=20=
*ident_idx_p)=0A=20=09=20*/=0A=20=09ident_idx=20=3D=20=
RelationGetReplicaIndex(rel);=0A=20=09if=20(!OidIsValid(ident_idx)=20&&=20=
OidIsValid(rel->rd_pkindex))=0A+=09{=0A+=09=09/*=0A+=09=09=20*=20A=20=
deferrable=20primary=20key=20is=20not=20considered=20a=20replica=20=
identity=0A+=09=09=20*=20index,=20so=20WAL=20may=20not=20contain=20the=20=
old=20tuple=20needed=20for=20replay.=0A+=09=09=20*/=0A+=09=09if=20=
(rel->rd_ispkdeferrable)=0A+=09=09=09ereport(ERROR,=0A+=09=09=09=09=09=
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),=0A+=09=09=09=09=09=
errmsg("cannot=20process=20relation=20\"%s\"",=0A+=09=09=09=09=09=09=20=20=
=20RelationGetRelationName(rel)),=0A+=09=09=09=09=09errhint("Relation=20=
\"%s\"=20has=20a=20deferrable=20primary=20key.",=0A+=09=09=09=09=09=09=09=
RelationGetRelationName(rel)));=0A+=0A=20=09=09ident_idx=20=3D=20=
rel->rd_pkindex;=0A+=09}=0A=20=09if=20(!OidIsValid(ident_idx))=0A=20=09=09=
ereport(ERROR,=0A=20=09=09=09=09=
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),=0A--=20=0A2.50.1=20=
(Apple=20Git-155)=0A=0A=

--Apple-Mail=_A69970C7-4B6D-4E61-9AC1-A45FFC4C10C4--