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

On Thu, Nov 12, 2026 at 10:52 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> 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.
>

Thanks for the report. I could reproduce it on master.

I agree that falling back to a sequential scan only when the hash key
is NULL seems the better fix.

On top of your suggested changes, the attached patch:
- adds the same fallback in RelationFindDeletedTupleInfoByIndex(), so
that update_deleted is detected for rows with a NULL key
- adds tests in 032_subscribe_use_index.pl and 035_conflicts.pl for both cases.

--
Thanks,
Nisha