BUG #19893: REPLICA IDENTITY FULL: UPDATE/DELETE skipped when a subscriber-only column has a value

The following bug has been logged on the website:

Bug reference: 19893
Logged by: Ke
Email address: kehan5800(at)gmail(dot)com
PostgreSQL version: 18.6
Operating system: Ubuntu 22.04 x86_64 (Linux 5.15)
Description:

The subscriber's table may have columns that the published table does not
have: "The target table can also have additional columns not provided by
the published table. Any such columns will be filled with the default value
as specified in the definition of the target table." (Logical Replication,
Subscription). If the published table has REPLICA IDENTITY FULL, the
subscriber's table has no primary key or replica identity index, and such
a subscriber-only column holds a value (a DEFAULT, an identity column, or a
value set on the subscriber), then no UPDATE or DELETE of the table is
applied. For each one the apply worker logs an update_missing or
delete_missing conflict (on 17, nothing at the default log level) and goes
on, so the subscriber keeps the old version of every updated row and every
deleted row. Inserts are replicated normally. A subscriber-only column that
is NULL does not cause this, and neither does the same column in a table
that has a primary key on the subscriber.

Script (two new clusters on ports 5446 and 5447; on the subscriber, t1 has
a subscriber-only column with a DEFAULT, t2 an identity column and a btree
index that is used for the lookups, and t3 a subscriber-only column without
a default):

---- repro.sh: PGBIN=/usr/local/pgsql/bin sh repro.sh /tmp/lrextra ----
#!/bin/sh
# usage: PGBIN=/usr/local/pgsql/bin sh repro.sh /tmp/lrextra
W=${1:?work directory}; PUB=5446; SUB=5447
rm -rf "$W"; mkdir -p "$W"
for n in pub sub; do
"$PGBIN/initdb" -D "$W/$n" -U postgres -A trust >/dev/null
printf "listen_addresses = ''\nunix_socket_directories = '%s'\nwal_level =
logical\n" "$W" >> "$W/$n/postgresql.conf"
done
"$PGBIN/pg_ctl" -D "$W/pub" -o "-p $PUB" -l "$W/pub.log" -w start >/dev/null
"$PGBIN/pg_ctl" -D "$W/sub" -o "-p $SUB" -l "$W/sub.log" -w start >/dev/null
pub() { "$PGBIN/psql" -X -q -At -h "$W" -p $PUB -U postgres -c "$1"; }
sub() { "$PGBIN/psql" -X -q -At -h "$W" -p $SUB -U postgres -c "$1"; }

for t in t1 t2 t3; do
pub "CREATE TABLE $t (a int, b text)"
pub "ALTER TABLE $t REPLICA IDENTITY FULL"
done
pub "CREATE PUBLICATION p FOR TABLE t1, t2, t3"
# subscriber: the same tables plus a column x that the publisher does not
have
sub "CREATE TABLE t1 (a int, b text, x text DEFAULT 'local')"
sub "CREATE TABLE t2 (a int, b text, x bigint GENERATED BY DEFAULT AS
IDENTITY)"
sub "CREATE INDEX ON t2 (a)" # t2 is searched through
this index
sub "CREATE TABLE t3 (a int, b text, x text)" # x stays NULL in t3
sub "CREATE SUBSCRIPTION s CONNECTION 'host=$W port=$PUB user=postgres
dbname=postgres' PUBLICATION p" 2>/dev/null
until [ "$(sub "SELECT count(*) FROM pg_subscription_rel WHERE srsubstate <>
'r'")" = 0 ]; do sleep 0.2; done

for t in t1 t2 t3; do
pub "INSERT INTO $t VALUES (1, 'one'), (2, 'two'), (3, 'three')"
pub "UPDATE $t SET b = 'one updated' WHERE a = 1"
pub "DELETE FROM $t WHERE a = 2"
done
pub "INSERT INTO t3 VALUES (99, 'marker')" # wait until the subscriber
has applied everything
until [ "$(sub "SELECT count(*) FROM t3 WHERE a = 99")" = 1 ]; do sleep 0.2;
done

echo "== publisher"
pub "SELECT 't1', * FROM t1 UNION ALL SELECT 't2', * FROM t2 UNION ALL
SELECT 't3', * FROM t3 WHERE a < 99 ORDER BY 1, 2"
echo "== subscriber"
sub "SELECT 't1', * FROM t1 UNION ALL SELECT 't2', a, b, x::text FROM t2
UNION ALL SELECT 't3', * FROM t3 WHERE a < 99 ORDER BY 1, 2"
echo "== subscriber log"
grep -E 'conflict detected|Could not find' "$W/sub.log" | sed
's/^[^[]*\[[0-9]*\] //'
"$PGBIN/pg_ctl" -D "$W/sub" -w stop >/dev/null
"$PGBIN/pg_ctl" -D "$W/pub" -w stop >/dev/null
---- end ----

Output on master (d0a4f66217, and bb8a88bbd1 before it); 19rc1 prints the
same:

== publisher
t1|1|one updated
t1|3|three
t2|1|one updated
t2|3|three
t3|1|one updated
t3|3|three
== subscriber
t1|1|one|local
t1|2|two|local
t1|3|three|local
t2|1|one|1
t2|2|two|2
t2|3|three|3
t3|1|one updated|
t3|3|three|
== subscriber log
LOG: conflict detected on relation "public.t1": conflict=update_missing
DETAIL: Could not find the row to be updated: remote row (1, one updated,
null), replica identity full (1, one, null).
LOG: conflict detected on relation "public.t1": conflict=delete_missing
DETAIL: Could not find the row to be deleted: replica identity full (2,
two, null).
LOG: conflict detected on relation "public.t2": conflict=update_missing
DETAIL: Could not find the row to be updated: remote row (1, one updated,
null), replica identity full (1, one, null).
LOG: conflict detected on relation "public.t2": conflict=delete_missing
DETAIL: Could not find the row to be deleted: replica identity full (2,
two, null).

Expected: t1 and t2 on the subscriber have the same rows as on the
publisher, (1, 'one updated') and (3, 'three'), each with its local x
value, as t3 does.

The DETAIL lines show what is searched for: "replica identity full (1, one,
null)", that is NULL for x, while the local row has x = 'local' (t1) or
x = 1 (t2).

The same state also arises during ordinary schema changes:

- The documentation suggests applying additive schema changes to the
subscriber first. For a REPLICA IDENTITY FULL table without a key on the
subscriber, ALTER TABLE t ADD COLUMN c int DEFAULT 0 on the subscriber
makes c a subscriber-only column with a value until the publisher runs
the same ALTER, and every UPDATE and DELETE replicated in between is
lost. With rows (1, 'one'), (2, 'two'), (3, 'three'), an UPDATE of row 1
and a DELETE of row 2 made between the two ALTERs are skipped; after the
publisher's ALTER and an UPDATE of row 3, the publisher has
1|one updated|0 and 3|three updated|0, the subscriber 1|one|0, 2|two|0
and 3|three updated|0, and the tables stay different.

- After ALTER TABLE t DROP COLUMN c on the publisher only (the usual order
for a subtractive change), the subscriber's c keeps its old values and is
now subscriber-only, so UPDATE and DELETE of every existing row whose c
is not NULL are skipped as long as the subscriber still has the column.

Versions tested (SELECT version()):
PostgreSQL 20devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
(master d0a4f66217, 2026-11-08, and bb8a88bbd1, 2026-11-06; ./configure
--enable-debug --without-icu)
PostgreSQL 19rc1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
PostgreSQL 17.11 (Debian 17.11-1.pgdg13+2) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
18.6 and 17.11 (PGDG packages in Debian 13 containers) give the same rows
with an equivalent script; 17 logs nothing at the default log level, since
it reports missing rows at DEBUG1. 14 to 16 build and compare the search
tuple the same way (from reading the code; not run there).

Cause, as far as I can tell:

apply_handle_update() and apply_handle_delete() in
src/backend/replication/logical/worker.c build the search tuple from the
old tuple with slot_store_data(), which sets every local column without a
remote counterpart to NULL (its comment says that "missing values should be
later filled using slot_fill_defaults", but nothing fills them in the search
tuple). Without a primary key or replica identity index on the subscriber,
FindReplTupleInLocalRel() calls RelationFindReplTupleSeq(), or
RelationFindReplTupleByIndex() for an index chosen by
FindUsableIndexForReplicaIdentityFull(), in
src/backend/executor/execReplication.c. Both compare each candidate with
tuples_equal(..., NULL), which compares every column of the local
descriptor except dropped and generated ones. A subscriber-only column with
a value never equals the NULL in the search tuple, so no row matches.

Commits b797def595 and adedf54e65 (2023) made tuples_equal() skip dropped
and generated columns because "the publisher doesn't send those"; columns
that the published table does not have are not sent either, but they are
still compared.

The scan key has the same problem: IsIndexUsableForReplicaIdentityFull()
only requires the leftmost index column to be published, and
build_replindex_scan_key() puts the NULL of the search tuple into the scan
key (SK_SEARCHNULL) for any other index column, so an index such as (a, x)
finds nothing even when the comparison is fixed.

Possible fix: compare only the local columns that have a remote
counterpart (attrmap->attnums[i] >= 0); tuples_equal() already takes a set
of columns to compare. For an index chosen for REPLICA IDENTITY FULL, leave
the other columns out of the scan key as well. The patch below does both for
the lookups made by FindReplTupleInLocalRel(). With it, the script above on
master gives the same rows on both sides and logs no conflict; an index on
(a, x) and the DROP COLUMN case above work too, and without the scan key
part the (a, x) index still finds nothing. The subscription TAP tests pass
with it (make -C src/test/subscription check: 39 files, 610 tests, on master
d0a4f66217). Two related places that use the same search tuple are not
changed by the patch:

- The update_deleted lookups (RelationFindDeletedTupleInfoSeq() and
RelationFindDeletedTupleInfoByIndex(), with retain_dead_tuples) compare
the same way.

- For a partitioned subscriber table, apply_handle_tuple_routing() chooses
the partition for UPDATE and DELETE with ExecFindPartition() on the search
tuple. If the partition key is a subscriber-only column with a default
(for example region text DEFAULT 'eu', PARTITION BY LIST (region), with
partitions for 'eu' and DEFAULT), inserts go to the 'eu' partition, but
UPDATE and DELETE look in the DEFAULT partition and are skipped as
update_missing / delete_missing, with or without the patch.

--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -58,7 +58,7 @@
*/
static int
build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
- TupleTableSlot *searchslot)
+ TupleTableSlot *searchslot,
Bitmapset *columns)
{
int index_attoff;
int skey_attoff = 0;
@@ -91,6 +91,15 @@
}

/*
+ * Skip columns not listed in 'columns'; the search tuple
has no value
+ * for them.
+ */
+ if (columns &&
+ !bms_is_member(table_attno -
FirstLowInvalidHeapAttributeNumber,
+ columns))
+ continue;
+
+ /*
* Load the operator info. We need this to get the equality
operator
* function for the scan key.
*/
@@ -186,13 +195,18 @@
* only the index key columns are compared, using the index's own equality
* semantics. If false, all matching tuples are compared against
'searchslot',
* which must contain a complete row.
+ *
+ * If 'columns' is not NULL, only those columns of 'searchslot' have values
+ * (for a REPLICA IDENTITY FULL lookup, the columns that the publisher
sends):
+ * the other columns are left out of the scan key and of the comparison.
*/
bool
RelationFindReplTupleByIndex(Relation rel, Oid idxoid,
bool
skipduplicates,
LockTupleMode
lockmode,
TupleTableSlot
*searchslot,
- TupleTableSlot
*outslot)
+ TupleTableSlot
*outslot,
+ Bitmapset *columns)
{
ScanKeyData skey[INDEX_MAX_KEYS];
int skey_attoff;
@@ -209,7 +223,8 @@
InitDirtySnapshot(snap);

/* Build scan key. */
- skey_attoff = build_replindex_scan_key(skey, rel, idxrel,
searchslot);
+ skey_attoff = build_replindex_scan_key(skey, rel, idxrel,
searchslot,
+
skipduplicates ? NULL : columns);

/* Start an index scan. */
scan = index_beginscan(rel, idxrel, false,
@@ -233,7 +248,7 @@
if (eq == NULL)
eq = palloc0_array(TypeCacheEntry *,
outslot->tts_tupleDescriptor->natts);

- if (!tuples_equal(outslot, searchslot, eq, NULL))
+ if (!tuples_equal(outslot, searchslot, eq, columns))
continue;
}
else if (scan->xs_recheck &&
@@ -420,11 +435,15 @@
*
* Note that this stops on the first matching tuple.
*
+ * If 'columns' is not NULL, only those columns are compared, as in
+ * RelationFindReplTupleByIndex().
+ *
* This can obviously be quite slow on tables that have more than few rows.
*/
bool
RelationFindReplTupleSeq(Relation rel, LockTupleMode lockmode,
- TupleTableSlot *searchslot,
TupleTableSlot *outslot)
+ TupleTableSlot *searchslot,
TupleTableSlot *outslot,
+ Bitmapset *columns)
{
TupleTableSlot *scanslot;
TableScanDesc scan;
@@ -452,7 +471,7 @@
/* Try to find the tuple */
while (table_scan_getnextslot(scan, ForwardScanDirection, scanslot))
{
- if (!tuples_equal(scanslot, searchslot, eq, NULL))
+ if (!tuples_equal(scanslot, searchslot, eq, columns))
continue;

found = true;
@@ -736,7 +755,7 @@
idxrel = index_open(idxoid, RowExclusiveLock);

/* Build scan key. */
- skey_attoff = build_replindex_scan_key(skey, rel, idxrel,
searchslot);
+ skey_attoff = build_replindex_scan_key(skey, rel, idxrel,
searchslot, NULL);

/*
* Start an index scan using SnapshotAny to identify dead tuples
that are
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -778,9 +778,11 @@
bool skipduplicates,
LockTupleMode lockmode,
TupleTableSlot *searchslot,
-
TupleTableSlot *outslot);
+
TupleTableSlot *outslot,
+
Bitmapset *columns);
extern bool RelationFindReplTupleSeq(Relation rel, LockTupleMode lockmode,
-
TupleTableSlot *searchslot, TupleTableSlot *outslot);
+
TupleTableSlot *searchslot, TupleTableSlot *outslot,
+
Bitmapset *columns);
extern bool RelationFindDeletedTupleInfoSeq(Relation rel, Oid identidxoid,
TupleTableSlot *searchslot,
TransactionId oldestxmin,
--- a/src/backend/replication/logical/worker.c
+++ b/src/backend/replication/logical/worker.c
@@ -3225,6 +3225,8 @@
{
EState *estate = edata->estate;
Oid localidxoid = relmapentry->localindexoid;
+ AttrMap *attrmap = relmapentry->attrmap;
+ Bitmapset *columns = NULL;
bool found;

/*
@@ -3235,6 +3237,18 @@

*localslot = table_slot_create(localrel, &estate->es_tupleTable);

+ /*
+ * The search tuple has values only for the local columns that the
+ * publisher sends; slot_store_data() sets the others to NULL. Only
those
+ * columns can be used to find the local row.
+ */
+ for (int i = 0; i < attrmap->maplen; i++)
+ {
+ if (attrmap->attnums[i] >= 0)
+ columns = bms_add_member(columns,
+ i +
1 - FirstLowInvalidHeapAttributeNumber);
+ }
+
Assert(OidIsValid(localidxoid) ||
(relmapentry->remoterel.replident ==
REPLICA_IDENTITY_FULL));

@@ -3270,11 +3284,12 @@
found = RelationFindReplTupleByIndex(localrel, localidxoid,
relmapentry->idxisreplident,
LockTupleExclusive,
-
remoteslot, *localslot);
+
remoteslot, *localslot,
+
columns);
}
else
found = RelationFindReplTupleSeq(localrel,
LockTupleExclusive,
-
remoteslot, *localslot);
+
remoteslot, *localslot, columns);

return found;
}

Workaround: a primary key or replica identity index on published columns
of the subscriber's table (the key lookup is not affected), or no values in
subscriber-only columns of such tables.