BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column

The following bug has been logged on the website:

Bug reference: 19892
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:

When a published table has REPLICA IDENTITY FULL and the subscriber's table
has no primary key or replica identity index, the apply worker looks up the
old row through the first suitable index of the subscriber's table, and
since PostgreSQL 17 that can be a hash index. If the indexed column is NULL
in the old row, the lookup finds nothing: the UPDATE or DELETE is not
applied, and the worker only logs an update_missing or delete_missing
conflict (on 17, nothing at the default log level). The subscription keeps
running, and the subscriber keeps the old version of the updated row and
the deleted row. With a btree index on the same column, or with no index
(sequential scan), the same changes are applied correctly.

The documentation lists hash indexes as candidates without a restriction on
NULL values: "Candidate indexes must be btree or hash, non-partial, and the
leftmost index field must be a column (not an expression) that references
the published table column." (Logical Replication, Replica Identity). The
comment above IsIndexUsableForReplicaIdentityFull() also says that, unlike
for primary keys, nullable columns are supported for these indexes.

Script (two new clusters on ports 5446 and 5447; the second argument selects
the subscriber's index: hash, btree or none):

---- repro.sh: PGBIN=/usr/local/pgsql/bin sh repro.sh /tmp/lrhash hash ----
#!/bin/sh
# usage: PGBIN=/usr/local/pgsql/bin sh repro.sh /tmp/lrhash
[hash|btree|none]
W=${1:?work directory}; AM=${2:-hash}; 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"; }

pub "CREATE TABLE t (a int, b text)"
pub "ALTER TABLE t REPLICA IDENTITY FULL"
pub "CREATE PUBLICATION p FOR TABLE t"
sub "CREATE TABLE t (a int, b text)"
[ "$AM" = none ] || sub "CREATE INDEX t_a_idx ON t USING $AM (a)"
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

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

echo "== publisher"; pub "SELECT a, b FROM t ORDER BY b"
echo "== subscriber (index: $AM)"; sub "SELECT a, b FROM t ORDER BY b"
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
99|marker
|null-1 updated
2|two updated
== subscriber (index: hash)
99|marker
|null-1
|null-2
2|two updated
== subscriber log
LOG: conflict detected on relation "public.t": conflict=update_missing
DETAIL: Could not find the row to be updated: remote row (null, null-1
updated), replica identity full (null, null-1).
LOG: conflict detected on relation "public.t": conflict=delete_missing
DETAIL: Could not find the row to be deleted: replica identity full (null,
null-2).

Expected: the subscriber has the same rows as the publisher. That is what
happens with "btree" or "none" as the second argument, and then no conflict
is logged:

== subscriber (index: btree)
99|marker
|null-1 updated
2|two updated

The rows with a non-NULL key are replicated correctly in every case. Later
changes of an affected row are skipped as well, so the subscriber does not
converge again: after appending UPDATE t SET b = 'null-1 again' WHERE b =
'null-1 updated' and UPDATE t SET a = 5 WHERE b = 'null-1 again' to the
script, the publisher has (5, 'null-1 again') and the subscriber still has
(NULL, 'null-1'), with two more update_missing conflicts.

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 the same statements; 17 logs nothing at the default log level, since it
reports missing rows at DEBUG1. Hash indexes have been accepted for these
lookups since commit edca342434 (17); 16 only uses btree indexes here.

Cause, as far as I can tell:

FindUsableIndexForReplicaIdentityFull() in
src/backend/replication/logical/relation.c returns the first valid index for
which IsIndexUsableForReplicaIdentityFull() is true. That function checks
for
a non-partial index, an equality strategy for every key column, a leftmost
column that is a published column, and amgettuple, but not amsearchnulls,
so the hash index is chosen.

build_replindex_scan_key() in src/backend/executor/execReplication.c turns a
NULL value of the search tuple into a scan key with SK_ISNULL |
SK_SEARCHNULL. That works for btree, but hash indexes do not store NULLs
(amsearchnulls is false), and _hash_first() returns no match for an
SK_ISNULL key ("If the constant in the index qual is NULL, assume it cannot
match any items in the index"). RelationFindReplTupleByIndex() therefore
finds no tuple, and apply_handle_update_internal() and
apply_handle_delete_internal() in src/backend/replication/logical/worker.c
report update_missing / delete_missing and skip the change.

Possible fix: either reject index AMs without amsearchnulls in
IsIndexUsableForReplicaIdentityFull() (simple, but then hash indexes are
never used for these lookups), or fall back to a sequential scan when a key
column of the search tuple is NULL and the index cannot search for NULLs.
The patch below does the latter in RelationFindReplTupleByIndex(). With it,
the script above on master gives the same rows on both sides and logs no
conflict, and the hash index is still used for the non-NULL keys. The
subscription TAP tests pass with it (make -C src/test/subscription check:
39 files, 610 tests, on master d0a4f66217).
RelationFindDeletedTupleInfoByIndex() (update_deleted detection with
retain_dead_tuples) builds the same scan key and would need the same check.

--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -175,6 +175,30 @@
}

/*
+ * Return true if a key column of 'idxrel' is NULL in 'searchslot' and the
+ * index access method cannot search for NULLs. An index scan with such a
+ * scan key returns nothing (hash indexes do not store NULLs).
+ */
+static bool
+replindex_key_unsearchable(Relation idxrel, TupleTableSlot *searchslot)
+{
+ int2vector *indkey = &idxrel->rd_index->indkey;
+
+ if (idxrel->rd_indam->amsearchnulls)
+ return false;
+
+ for (int i = 0; i < IndexRelationGetNumberOfKeyAttributes(idxrel);
i++)
+ {
+ AttrNumber attno = indkey->values[i];
+
+ if (AttributeNumberIsValid(attno) &&
searchslot->tts_isnull[attno - 1])
+ return true;
+ }
+
+ return false;
+}
+
+/*
* Search the relation 'rel' for tuple using the index.
*
* If a matching tuple is found, lock it with lockmode, fill the slot with
its
@@ -206,6 +230,18 @@
/* Open the index. */
idxrel = index_open(idxoid, RowExclusiveLock);

+ /*
+ * If the index cannot search for a NULL key value, find the tuple
with a
+ * sequential scan instead. Only an index chosen for REPLICA
IDENTITY
+ * FULL can have nullable key columns, and then 'searchslot' holds
the
+ * whole row.
+ */
+ if (!skipduplicates && replindex_key_unsearchable(idxrel,
searchslot))
+ {
+ index_close(idxrel, NoLock);
+ return RelationFindReplTupleSeq(rel, lockmode, searchslot,
outslot);
+ }
+
InitDirtySnapshot(snap);

/* Build scan key. */

Workaround: use a btree index instead of the hash index on the subscriber.

The recent fixes in this code path (ea9c9432c8 for invalid indexes,
ad36e3608c for concurrent index DDL, 3cde90ecc5 for lossy GiST identity
indexes) do not change this case; master still behaves as shown above.