pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column
8+ messages / 4 participants
[nested] [flat]

* BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column
@ 2026-11-11 22:19 PG Bug reporting form <noreply@postgresql.org>
  2026-11-13 11:32 ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: PG Bug reporting form @ 2026-11-11 22:19 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: kehan5800@gmail.com

The following bug has been logged on the website:

Bug reference:      19892
Logged by:          Ke
Email address:      kehan5800@gmail.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.

inline patch 1: src/backend/executor/execReplication.c (+13/-0 lines) download inline-1.patch--- 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. ^ permalink raw reply [nested|flat] 8+ messages in thread

* Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column
  2026-11-11 22:19 BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column PG Bug reporting form <noreply@postgresql.org>
@ 2026-11-13 11:32 ` Nisha Moond <nisha.moond412@gmail.com>
  2026-11-16 08:01   ` RE: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Nisha Moond @ 2026-11-13 11:32 UTC (permalink / raw)
  To: kehan5800@gmail.com; pgsql-bugs@lists.postgresql.org

On Thu, Nov 12, 2026 at 10:52 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      19892
> Logged by:          Ke
> Email address:      kehan5800@gmail.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

Attachments:

v1-0001-Fix-lookup-of-rows-with-NULL-keys-through-a-hash-.patch (application/octet-stream, 10.2K) download From 7e0f1ce72dbcc39e772c632cccd6380b49c4bdcd Mon Sep 17 00:00:00 2001 From: Nisha Moond <nisha.moond412@gmail.com> Date: Fri, 13 Nov 2026 16:50:46 +0530 Subject: [PATCH v1] Fix lookup of rows with NULL keys through a hash index in logical replication. With REPLICA IDENTITY FULL, the apply worker can use a hash index on the subscriber to find the old row of an UPDATE or DELETE. Hash indexes don't store NULLs, so when the indexed column is NULL in the old row, the index scan finds nothing. The change was then skipped and reported as update_missing or delete_missing, leaving the subscriber out of sync. Fall back to a sequential scan in that case. Rows with non-NULL keys still use the index. Do the same when searching for a deleted row to detect update_deleted conflicts with retain_dead_tuples. --- src/backend/executor/execReplication.c | 44 ++++++++++ .../subscription/t/032_subscribe_use_index.pl | 80 +++++++++++++++++++ src/test/subscription/t/035_conflicts.pl | 67 ++++++++++++++++ 3 files changed, 191 insertions(+) diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c index 66a788e9e3d..f1b11e215b5 100644 --- a/src/backend/executor/execReplication.c +++ b/src/backend/executor/execReplication.c @@ -175,6 +175,29 @@ should_refetch_tuple(TM_Result res, TM_FailureData *tmfd) return refetch; } +/* + * Return true if 'searchslot' has a NULL in a key column of 'idxrel' and the + * index can't search for NULLs (e.g. hash). Such a scan finds nothing. + */ +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. * @@ -207,6 +230,17 @@ RelationFindReplTupleByIndex(Relation rel, Oid idxoid, /* Open the index. */ idxrel = index_open(idxoid, RowExclusiveLock); + /* + * The index can't find this row, so use a sequential scan. + * !skipduplicates means the index was chosen for REPLICA IDENTITY FULL, + * so 'searchslot' has 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. */ @@ -736,6 +770,16 @@ RelationFindDeletedTupleInfoByIndex(Relation rel, Oid idxoid, idxrel = index_open(idxoid, RowExclusiveLock); + /* The index can't find this row, so use a sequential scan. */ + if (!skipduplicates && replindex_key_unsearchable(idxrel, searchslot)) + { + index_close(idxrel, NoLock); + ExecDropSingleTupleTableSlot(scanslot); + return RelationFindDeletedTupleInfoSeq(rel, InvalidOid, searchslot, + oldestxmin, delete_xid, + delete_origin, delete_time); + } + /* Build scan key. */ skey_attoff = build_replindex_scan_key(skey, rel, idxrel, searchslot); diff --git a/src/test/subscription/t/032_subscribe_use_index.pl b/src/test/subscription/t/032_subscribe_use_index.pl index bd80465f727..a5becaab3ff 100644 --- a/src/test/subscription/t/032_subscribe_use_index.pl +++ b/src/test/subscription/t/032_subscribe_use_index.pl @@ -606,6 +606,86 @@ $node_subscriber->safe_psql('postgres', "DROP TABLE test_replica_id_full"); # Testcase end: Subscription can use hash index # ============================================================================= +# ============================================================================= +# Testcase start: Subscription finds rows with NULL keys when using hash index +# +# Hash indexes do not store NULLs, so an index scan for a NULL key value finds +# nothing. UPDATE/DELETE of a row whose hash-indexed column is NULL must still +# be applied, rather than being reported as update_missing/delete_missing. + +# create table, initial data (including rows with NULL in the indexed column) +# and publication on pub +$node_publisher->safe_psql( + 'postgres', q[ + CREATE TABLE test_hash_null (x int, y text); + ALTER TABLE test_hash_null REPLICA IDENTITY FULL; + INSERT INTO test_hash_null VALUES + (1, 'one'), (2, 'two'), + (NULL, 'null-1'), (NULL, 'null-2'), (NULL, 'null-3'); + CREATE PUBLICATION tap_pub_hash_null FOR TABLE test_hash_null; +]); + +# create table, hash index and subscription on sub +$node_subscriber->safe_psql( + 'postgres', qq[ + CREATE TABLE test_hash_null (x int, y text); + CREATE INDEX test_hash_null_idx ON test_hash_null USING HASH (x); + CREATE SUBSCRIPTION tap_sub_hash_null CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub_hash_null; +]); + +# wait for initial table synchronization to finish +$node_subscriber->wait_for_subscription_sync($node_publisher, $appname); + +# update a row with a non-NULL key, and make sure the hash index is used +$node_publisher->safe_psql('postgres', + "UPDATE test_hash_null SET y = 'two updated' WHERE x = 2"); +$node_publisher->wait_for_catchup($appname); +$node_subscriber->poll_query_until('postgres', + q{select (idx_scan = 1) from pg_stat_all_indexes where indexrelname = 'test_hash_null_idx';} + ) + or die + "Timed out while waiting for check subscriber tap_sub_hash_null updates one row via index"; + +# update and delete rows with a NULL key, and set the key of a row with a +# NULL key to a non-NULL value +$node_publisher->safe_psql( + 'postgres', q[ + UPDATE test_hash_null SET y = 'null-1 updated' WHERE y = 'null-1'; + DELETE FROM test_hash_null WHERE y = 'null-2'; + UPDATE test_hash_null SET x = 3 WHERE y = 'null-3'; +]); +$node_publisher->wait_for_catchup($appname); + +# make sure that the subscriber has the same data as the publisher +my $pub_result = $node_publisher->safe_psql('postgres', + "SELECT x, y FROM test_hash_null ORDER BY y"); +$result = $node_subscriber->safe_psql('postgres', + "SELECT x, y FROM test_hash_null ORDER BY y"); +is($result, $pub_result, + 'ensure subscriber applies UPDATE/DELETE of rows with NULL in hash-indexed column' +); +is( $result, qq(|null-1 updated +3|null-3 +1|one +2|two updated), + 'ensure subscriber has the correct data at the end of the test'); + +# cleanup pub +$node_publisher->safe_psql( + 'postgres', q[ + DROP PUBLICATION tap_pub_hash_null; + DROP TABLE test_hash_null; +]); +# cleanup sub +$node_subscriber->safe_psql( + 'postgres', q[ + DROP SUBSCRIPTION tap_sub_hash_null; + DROP TABLE test_hash_null; +]); + +# Testcase end: Subscription finds rows with NULL keys when using hash index +# ============================================================================= + # ============================================================================= # Testcase start: Subscription keeps using an index that concurrent DDL has # demoted from replica identity diff --git a/src/test/subscription/t/035_conflicts.pl b/src/test/subscription/t/035_conflicts.pl index 48ce95d9d13..c133a08d530 100644 --- a/src/test/subscription/t/035_conflicts.pl +++ b/src/test/subscription/t/035_conflicts.pl @@ -525,6 +525,73 @@ $node_A->safe_psql('postgres', $node_A->safe_psql('postgres', "DROP TABLE tab_mr"); $node_B->safe_psql('postgres', "DROP TABLE tab_mr"); +############################################################################### +# Check that the search for a deleted tuple finds a row whose key is NULL in a +# hash index chosen for REPLICA IDENTITY FULL. Hash indexes do not store NULLs, +# so the row must be found without the index scan, and the conflict reported +# as update_deleted instead of update_missing. +############################################################################### + +# Publisher table holding the row that will be updated. +$node_B->safe_psql( + 'postgres', " + CREATE TABLE tab_hash_null (a int, b text); + ALTER TABLE tab_hash_null REPLICA IDENTITY FULL; + INSERT INTO tab_hash_null VALUES (NULL, 'null-1'); + ALTER PUBLICATION tap_pub_B ADD TABLE tab_hash_null; +"); + +# Subscriber table with the hash index. The index is created before the +# conflict detection slot's xmin is pinned below, so that it can be used to +# find deleted tuples. +$node_A->safe_psql( + 'postgres', " + CREATE TABLE tab_hash_null (a int, b text); + CREATE INDEX tab_hash_null_idx ON tab_hash_null USING HASH (a); + ALTER SUBSCRIPTION $subname_AB REFRESH PUBLICATION; +"); +$node_A->wait_for_subscription_sync($node_B, $subname_AB); + +# Pin the conflict detection slot's xmin, as above. +$holder = $node_A->background_psql('postgres'); +$holder->query_safe('BEGIN'); +$pinned_xid = $holder->query_safe('SELECT txid_current()'); + +ok( $node_A->poll_query_until( + 'postgres', + "SELECT xmin = $pinned_xid FROM pg_replication_slots WHERE slot_name = 'pg_conflict_detection'" + ), + "conflict detection slot xmin pinned at $pinned_xid"); + +# Delete the row locally, then update it on the publisher. +$node_A->safe_psql('postgres', + "DELETE FROM tab_hash_null WHERE b = 'null-1'"); + +$log_location = -s $node_A->logfile; + +$node_B->safe_psql('postgres', + "UPDATE tab_hash_null SET b = 'null-1 updated' WHERE b = 'null-1'"); + +$node_B->wait_for_catchup($subname_AB); + +$logfile = slurp_file($node_A->logfile(), $log_location); +like( + $logfile, + qr/conflict detected on relation "public.tab_hash_null": conflict=update_deleted.* +.*DETAIL:.* Could not find the row to be updated: remote row \(null, null-1 updated\), replica identity full \(null, null-1\). +.*The row to be updated was deleted locally in transaction [0-9]+ at .*/, + 'update target row with NULL key was deleted in tab_hash_null'); + +# Clean up +$holder->query_safe('COMMIT'); +ok($holder->quit, 'release the pinned transaction'); +$node_B->safe_psql('postgres', + "ALTER PUBLICATION tap_pub_B DROP TABLE tab_hash_null"); +$node_A->safe_psql('postgres', + "ALTER SUBSCRIPTION $subname_AB REFRESH PUBLICATION"); +$node_A->safe_psql('postgres', "DROP TABLE tab_hash_null"); +$node_B->safe_psql('postgres', "DROP TABLE tab_hash_null"); + ############################################################################### # Check that the xmin value of the conflict detection slot can be advanced when # the subscription has no tables. -- 2.54.0 (Apple Git-157)
^ permalink raw reply [nested|flat] 8+ messages in thread

* RE: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column
  2026-11-11 22:19 BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column PG Bug reporting form <noreply@postgresql.org>
  2026-11-13 11:32 ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
@ 2026-11-16 08:01   ` Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
  2026-11-16 12:19     ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Hayato Kuroda (Fujitsu) @ 2026-11-16 08:01 UTC (permalink / raw)
  To: Nisha Moond <nisha.moond412@gmail.com>; kehan5800@gmail.com <kehan5800@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

Hi Ke and Nisha,

Good catch and thanks for working on it.
I was an author of edca3424342, which allowed to use hash index while finding
tuples for REPLICA IDENTITY FULL case. Sadly, I have not known the handling of
NULL by hash index, my fault.

I reviewed the proposed patch and basically LGTM. As for the test code, I confirmed
they could fail before fixing the source and can pass after that.
Few comments:

01.
```
+/*
+ * Return true if 'searchslot' has a NULL in a key column of 'idxrel' and the
+ * index can't search for NULLs (e.g. hash). Such a scan finds nothing.
+ */
+static bool
+replindex_key_unsearchable(Relation idxrel, TupleTableSlot *searchslot)
```

02.
```
+       /*
+        * The index can't find this row, so use a sequential scan.
+        * !skipduplicates means the index was chosen for REPLICA IDENTITY FULL,
+        * so 'searchslot' has the whole row.
+        */
+       if (!skipduplicates && replindex_key_unsearchable(idxrel, searchslot))
+       {
+               index_close(idxrel, NoLock);
+               return RelationFindReplTupleSeq(rel, lockmode, searchslot, outslot);
+       }
```

This comment looks bit strange because it does not describe why the index cannot
find the row. How about:

```
	/*
	 * Fall back to a sequential scan if the index cannot search for a NULL
	 * key. !skipduplicates means the index was chosen for REPLICA IDENTITY
	 * FULL, so 'searchslot' has the whole row.
	 */

```

03.
```
+       /* The index can't find this row, so use a sequential scan. */
+       if (!skipduplicates && replindex_key_unsearchable(idxrel, searchslot))
+       {
```

This can also adjust like:

```
	/*
	 * Fall back to a sequential scan if the index cannot search for a NULL
	 * key. See RelationFindReplTupleByIndex().
	 */
```


Best regards,
Hayato Kuroda
FUJITSU LIMITED



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column
  2026-11-11 22:19 BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column PG Bug reporting form <noreply@postgresql.org>
  2026-11-13 11:32 ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
  2026-11-16 08:01   ` RE: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
@ 2026-11-16 12:19     ` Nisha Moond <nisha.moond412@gmail.com>
  2026-11-16 13:08       ` RE: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Nisha Moond @ 2026-11-16 12:19 UTC (permalink / raw)
  To: Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>; +Cc: kehan5800@gmail.com <kehan5800@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

On Mon, Nov 16, 2026 at 1:31 PM Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.com> wrote:
>
> Hi Ke and Nisha,
>
> Good catch and thanks for working on it.
> I was an author of edca3424342, which allowed to use hash index while finding
> tuples for REPLICA IDENTITY FULL case. Sadly, I have not known the handling of
> NULL by hash index, my fault.
>
> I reviewed the proposed patch and basically LGTM. As for the test code, I confirmed
> they could fail before fixing the source and can pass after that.
> Few comments:
>
> 01.
> ```
> +/*
> + * Return true if 'searchslot' has a NULL in a key column of 'idxrel' and the
> + * index can't search for NULLs (e.g. hash). Such a scan finds nothing.
> + */
> +static bool
> +replindex_key_unsearchable(Relation idxrel, TupleTableSlot *searchslot)
> ```
>

I don't see a comment under this one. Did you mean to suggest a change?

--
Nisha






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* RE: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column
  2026-11-11 22:19 BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column PG Bug reporting form <noreply@postgresql.org>
  2026-11-13 11:32 ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
  2026-11-16 08:01   ` RE: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
  2026-11-16 12:19     ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
@ 2026-11-16 13:08       ` Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
  2026-11-16 15:15         ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Hayato Kuroda (Fujitsu) @ 2026-11-16 13:08 UTC (permalink / raw)
  To: Nisha Moond <nisha.moond412@gmail.com>; +Cc: kehan5800@gmail.com <kehan5800@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

Hi,

> I don't see a comment under this one. Did you mean to suggest a change?

Opps, my mailer might not work well.

> > 01.
> > ```
> > +/*
> > + * Return true if 'searchslot' has a NULL in a key column of 'idxrel' and the
> > + * index can't search for NULLs (e.g. hash). Such a scan finds nothing.
> > + */
> > +static bool
> > +replindex_key_unsearchable(Relation idxrel, TupleTableSlot *searchslot)
> > ```

The function name might be too strong because it only checks whether the index
can take care NULL and/or the searchslot has NULL column. How about
replindex_key_has_unsearchable_null() or similar? We can rename again if we
find one or two more reasons to choose a fallback seq scan.

Best regards,
Hayato Kuroda
FUJITSU LIMITED



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column
  2026-11-11 22:19 BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column PG Bug reporting form <noreply@postgresql.org>
  2026-11-13 11:32 ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
  2026-11-16 08:01   ` RE: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
  2026-11-16 12:19     ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
  2026-11-16 13:08       ` RE: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
@ 2026-11-16 15:15         ` Nisha Moond <nisha.moond412@gmail.com>
  2026-11-16 15:46           ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Dilip Kumar <dilipbalaut@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Nisha Moond @ 2026-11-16 15:15 UTC (permalink / raw)
  To: Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>; +Cc: kehan5800@gmail.com <kehan5800@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

 On Mon, Nov 16, 2026 at 6:38 PM Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.com> wrote:
>
> Hi,
>
> > I don't see a comment under this one. Did you mean to suggest a change?
>
> Opps, my mailer might not work well.
>
> > > 01.
> > > ```
> > > +/*
> > > + * Return true if 'searchslot' has a NULL in a key column of 'idxrel' and the
> > > + * index can't search for NULLs (e.g. hash). Such a scan finds nothing.
> > > + */
> > > +static bool
> > > +replindex_key_unsearchable(Relation idxrel, TupleTableSlot *searchslot)
> > > ```
>
> The function name might be too strong because it only checks whether the index
> can take care NULL and/or the searchslot has NULL column. How about
> replindex_key_has_unsearchable_null() or similar? We can rename again if we
> find one or two more reasons to choose a fallback seq scan.
>

Thanks for the review, renamed it to replindex_key_has_unsearchable_null().

I also made the suggested changes 02 & 03 at [1]. The updated patch is attached.

[1] https://www.postgresql.org/message-id/OS7PR01MB1831731C0A17AAED9DA406AAEF57C2%40OS7PR01MB18317.jpnpr...
--
Thanks,
Nisha

Attachments:

v2-0001-Fix-lookup-of-rows-with-NULL-keys-through-a-hash-.patch (application/octet-stream, 10.3K) download From 43511006d5b2dc9247e525f3a456238d70b8d443 Mon Sep 17 00:00:00 2001 From: Nisha Moond <nisha.moond412@gmail.com> Date: Fri, 13 Nov 2026 16:50:46 +0530 Subject: [PATCH v2] Fix lookup of rows with NULL keys through a hash index in logical replication. With REPLICA IDENTITY FULL, the apply worker can use a hash index on the subscriber to find the old row of an UPDATE or DELETE. Hash indexes don't store NULLs, so when the indexed column is NULL in the old row, the index scan finds nothing. The change was then skipped and reported as update_missing or delete_missing, leaving the subscriber out of sync. Fall back to a sequential scan in that case. Rows with non-NULL keys still use the index. Do the same when searching for a deleted row to detect update_deleted conflicts with retain_dead_tuples. --- src/backend/executor/execReplication.c | 49 ++++++++++++ .../subscription/t/032_subscribe_use_index.pl | 80 +++++++++++++++++++ src/test/subscription/t/035_conflicts.pl | 67 ++++++++++++++++ 3 files changed, 196 insertions(+) diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c index 66a788e9e3d..a818a92a0cd 100644 --- a/src/backend/executor/execReplication.c +++ b/src/backend/executor/execReplication.c @@ -175,6 +175,29 @@ should_refetch_tuple(TM_Result res, TM_FailureData *tmfd) return refetch; } +/* + * Return true if 'searchslot' has a NULL in a key column of 'idxrel' and the + * index can't search for NULLs (e.g. hash). Such a scan finds nothing. + */ +static bool +replindex_key_has_unsearchable_null(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. * @@ -207,6 +230,18 @@ RelationFindReplTupleByIndex(Relation rel, Oid idxoid, /* Open the index. */ idxrel = index_open(idxoid, RowExclusiveLock); + /* + * Fall back to a sequential scan if the index cannot search for a NULL + * key. !skipduplicates means the index was chosen for REPLICA IDENTITY + * FULL, so 'searchslot' has the whole row. + */ + if (!skipduplicates && + replindex_key_has_unsearchable_null(idxrel, searchslot)) + { + index_close(idxrel, NoLock); + return RelationFindReplTupleSeq(rel, lockmode, searchslot, outslot); + } + InitDirtySnapshot(snap); /* Build scan key. */ @@ -736,6 +771,20 @@ RelationFindDeletedTupleInfoByIndex(Relation rel, Oid idxoid, idxrel = index_open(idxoid, RowExclusiveLock); + /* + * Fall back to a sequential scan if the index cannot search for a NULL + * key. See RelationFindReplTupleByIndex(). + */ + if (!skipduplicates && + replindex_key_has_unsearchable_null(idxrel, searchslot)) + { + index_close(idxrel, NoLock); + ExecDropSingleTupleTableSlot(scanslot); + return RelationFindDeletedTupleInfoSeq(rel, InvalidOid, searchslot, + oldestxmin, delete_xid, + delete_origin, delete_time); + } + /* Build scan key. */ skey_attoff = build_replindex_scan_key(skey, rel, idxrel, searchslot); diff --git a/src/test/subscription/t/032_subscribe_use_index.pl b/src/test/subscription/t/032_subscribe_use_index.pl index bd80465f727..a5becaab3ff 100644 --- a/src/test/subscription/t/032_subscribe_use_index.pl +++ b/src/test/subscription/t/032_subscribe_use_index.pl @@ -606,6 +606,86 @@ $node_subscriber->safe_psql('postgres', "DROP TABLE test_replica_id_full"); # Testcase end: Subscription can use hash index # ============================================================================= +# ============================================================================= +# Testcase start: Subscription finds rows with NULL keys when using hash index +# +# Hash indexes do not store NULLs, so an index scan for a NULL key value finds +# nothing. UPDATE/DELETE of a row whose hash-indexed column is NULL must still +# be applied, rather than being reported as update_missing/delete_missing. + +# create table, initial data (including rows with NULL in the indexed column) +# and publication on pub +$node_publisher->safe_psql( + 'postgres', q[ + CREATE TABLE test_hash_null (x int, y text); + ALTER TABLE test_hash_null REPLICA IDENTITY FULL; + INSERT INTO test_hash_null VALUES + (1, 'one'), (2, 'two'), + (NULL, 'null-1'), (NULL, 'null-2'), (NULL, 'null-3'); + CREATE PUBLICATION tap_pub_hash_null FOR TABLE test_hash_null; +]); + +# create table, hash index and subscription on sub +$node_subscriber->safe_psql( + 'postgres', qq[ + CREATE TABLE test_hash_null (x int, y text); + CREATE INDEX test_hash_null_idx ON test_hash_null USING HASH (x); + CREATE SUBSCRIPTION tap_sub_hash_null CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub_hash_null; +]); + +# wait for initial table synchronization to finish +$node_subscriber->wait_for_subscription_sync($node_publisher, $appname); + +# update a row with a non-NULL key, and make sure the hash index is used +$node_publisher->safe_psql('postgres', + "UPDATE test_hash_null SET y = 'two updated' WHERE x = 2"); +$node_publisher->wait_for_catchup($appname); +$node_subscriber->poll_query_until('postgres', + q{select (idx_scan = 1) from pg_stat_all_indexes where indexrelname = 'test_hash_null_idx';} + ) + or die + "Timed out while waiting for check subscriber tap_sub_hash_null updates one row via index"; + +# update and delete rows with a NULL key, and set the key of a row with a +# NULL key to a non-NULL value +$node_publisher->safe_psql( + 'postgres', q[ + UPDATE test_hash_null SET y = 'null-1 updated' WHERE y = 'null-1'; + DELETE FROM test_hash_null WHERE y = 'null-2'; + UPDATE test_hash_null SET x = 3 WHERE y = 'null-3'; +]); +$node_publisher->wait_for_catchup($appname); + +# make sure that the subscriber has the same data as the publisher +my $pub_result = $node_publisher->safe_psql('postgres', + "SELECT x, y FROM test_hash_null ORDER BY y"); +$result = $node_subscriber->safe_psql('postgres', + "SELECT x, y FROM test_hash_null ORDER BY y"); +is($result, $pub_result, + 'ensure subscriber applies UPDATE/DELETE of rows with NULL in hash-indexed column' +); +is( $result, qq(|null-1 updated +3|null-3 +1|one +2|two updated), + 'ensure subscriber has the correct data at the end of the test'); + +# cleanup pub +$node_publisher->safe_psql( + 'postgres', q[ + DROP PUBLICATION tap_pub_hash_null; + DROP TABLE test_hash_null; +]); +# cleanup sub +$node_subscriber->safe_psql( + 'postgres', q[ + DROP SUBSCRIPTION tap_sub_hash_null; + DROP TABLE test_hash_null; +]); + +# Testcase end: Subscription finds rows with NULL keys when using hash index +# ============================================================================= + # ============================================================================= # Testcase start: Subscription keeps using an index that concurrent DDL has # demoted from replica identity diff --git a/src/test/subscription/t/035_conflicts.pl b/src/test/subscription/t/035_conflicts.pl index 48ce95d9d13..c133a08d530 100644 --- a/src/test/subscription/t/035_conflicts.pl +++ b/src/test/subscription/t/035_conflicts.pl @@ -525,6 +525,73 @@ $node_A->safe_psql('postgres', $node_A->safe_psql('postgres', "DROP TABLE tab_mr"); $node_B->safe_psql('postgres', "DROP TABLE tab_mr"); +############################################################################### +# Check that the search for a deleted tuple finds a row whose key is NULL in a +# hash index chosen for REPLICA IDENTITY FULL. Hash indexes do not store NULLs, +# so the row must be found without the index scan, and the conflict reported +# as update_deleted instead of update_missing. +############################################################################### + +# Publisher table holding the row that will be updated. +$node_B->safe_psql( + 'postgres', " + CREATE TABLE tab_hash_null (a int, b text); + ALTER TABLE tab_hash_null REPLICA IDENTITY FULL; + INSERT INTO tab_hash_null VALUES (NULL, 'null-1'); + ALTER PUBLICATION tap_pub_B ADD TABLE tab_hash_null; +"); + +# Subscriber table with the hash index. The index is created before the +# conflict detection slot's xmin is pinned below, so that it can be used to +# find deleted tuples. +$node_A->safe_psql( + 'postgres', " + CREATE TABLE tab_hash_null (a int, b text); + CREATE INDEX tab_hash_null_idx ON tab_hash_null USING HASH (a); + ALTER SUBSCRIPTION $subname_AB REFRESH PUBLICATION; +"); +$node_A->wait_for_subscription_sync($node_B, $subname_AB); + +# Pin the conflict detection slot's xmin, as above. +$holder = $node_A->background_psql('postgres'); +$holder->query_safe('BEGIN'); +$pinned_xid = $holder->query_safe('SELECT txid_current()'); + +ok( $node_A->poll_query_until( + 'postgres', + "SELECT xmin = $pinned_xid FROM pg_replication_slots WHERE slot_name = 'pg_conflict_detection'" + ), + "conflict detection slot xmin pinned at $pinned_xid"); + +# Delete the row locally, then update it on the publisher. +$node_A->safe_psql('postgres', + "DELETE FROM tab_hash_null WHERE b = 'null-1'"); + +$log_location = -s $node_A->logfile; + +$node_B->safe_psql('postgres', + "UPDATE tab_hash_null SET b = 'null-1 updated' WHERE b = 'null-1'"); + +$node_B->wait_for_catchup($subname_AB); + +$logfile = slurp_file($node_A->logfile(), $log_location); +like( + $logfile, + qr/conflict detected on relation "public.tab_hash_null": conflict=update_deleted.* +.*DETAIL:.* Could not find the row to be updated: remote row \(null, null-1 updated\), replica identity full \(null, null-1\). +.*The row to be updated was deleted locally in transaction [0-9]+ at .*/, + 'update target row with NULL key was deleted in tab_hash_null'); + +# Clean up +$holder->query_safe('COMMIT'); +ok($holder->quit, 'release the pinned transaction'); +$node_B->safe_psql('postgres', + "ALTER PUBLICATION tap_pub_B DROP TABLE tab_hash_null"); +$node_A->safe_psql('postgres', + "ALTER SUBSCRIPTION $subname_AB REFRESH PUBLICATION"); +$node_A->safe_psql('postgres', "DROP TABLE tab_hash_null"); +$node_B->safe_psql('postgres', "DROP TABLE tab_hash_null"); + ############################################################################### # Check that the xmin value of the conflict detection slot can be advanced when # the subscription has no tables. -- 2.54.0 (Apple Git-157)
^ permalink raw reply [nested|flat] 8+ messages in thread

* Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column
  2026-11-11 22:19 BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column PG Bug reporting form <noreply@postgresql.org>
  2026-11-13 11:32 ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
  2026-11-16 08:01   ` RE: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
  2026-11-16 12:19     ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
  2026-11-16 13:08       ` RE: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
  2026-11-16 15:15         ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
@ 2026-11-16 15:46           ` Dilip Kumar <dilipbalaut@gmail.com>
  2026-11-17 15:32             ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Dilip Kumar @ 2026-11-16 15:46 UTC (permalink / raw)
  To: Nisha Moond <nisha.moond412@gmail.com>; +Cc: Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>; kehan5800@gmail.com <kehan5800@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

On Mon, Nov 16, 2026 at 8:46 PM Nisha Moond <nisha.moond412@gmail.com> wrote:
>
>  On Mon, Nov 16, 2026 at 6:38 PM Hayato Kuroda (Fujitsu)
> <kuroda.hayato@fujitsu.com> wrote:
> >
> > Hi,
> >
> > > I don't see a comment under this one. Did you mean to suggest a change?
> >
> > Opps, my mailer might not work well.
> >
> > > > 01.
> > > > ```
> > > > +/*
> > > > + * Return true if 'searchslot' has a NULL in a key column of 'idxrel' and the
> > > > + * index can't search for NULLs (e.g. hash). Such a scan finds nothing.
> > > > + */
> > > > +static bool
> > > > +replindex_key_unsearchable(Relation idxrel, TupleTableSlot *searchslot)
> > > > ```
> >
> > The function name might be too strong because it only checks whether the index
> > can take care NULL and/or the searchslot has NULL column. How about
> > replindex_key_has_unsearchable_null() or similar? We can rename again if we
> > find one or two more reasons to choose a fallback seq scan.
> >
>
> Thanks for the review, renamed it to replindex_key_has_unsearchable_null().
>
> I also made the suggested changes 02 & 03 at [1]. The updated patch is attached.
>

Hi Nisha,

At high level the patch LGTM, a couple of minor comments:

1.
In RelationFindDeletedTupleInfoByIndex():
{
...
scanslot = table_slot_create(rel, NULL);
idxrel = index_open(idxoid, RowExclusiveLock);
if (!skipduplicates &&
replindex_key_has_unsearchable_null(idxrel, searchslot))
{
index_close(idxrel, NoLock);
ExecDropSingleTupleTableSlot(scanslot);
return RelationFindDeletedTupleInfoSeq()
}
Moving `scanslot = table_slot_create(rel, NULL);` below the fallback check
avoids allocating and immediately dropping the slot on every NULL-key
fallback.

2.
In doc/src/sgml/logical-replication.sgml, in the section below[1] it
may be worth briefly noting that "lookups for
rows with NULL in a hash-indexed column fall back to a sequential scan." ?

[1]
    <para>
     When using
     <link linkend="sql-altertable-replica-identity-full"><literal>REPLICA
IDENTITY FULL</literal></link>
     on published tables, it is important to note that the
<literal>UPDATE</literal>
     and <literal>DELETE</literal> operations cannot be applied to subscribers
     if the tables include attributes with datatypes (such as point or box)
     that do not have a default operator class for B-tree or Hash. However,
     this limitation can be overcome by ensuring that the table has a primary
     key or replica identity defined for it.
    </para>


-- 
Regards,
Dilip Kumar
Google






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column
  2026-11-11 22:19 BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column PG Bug reporting form <noreply@postgresql.org>
  2026-11-13 11:32 ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
  2026-11-16 08:01   ` RE: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
  2026-11-16 12:19     ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
  2026-11-16 13:08       ` RE: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
  2026-11-16 15:15         ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Nisha Moond <nisha.moond412@gmail.com>
  2026-11-16 15:46           ` Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column Dilip Kumar <dilipbalaut@gmail.com>
@ 2026-11-17 15:32             ` Nisha Moond <nisha.moond412@gmail.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Nisha Moond @ 2026-11-17 15:32 UTC (permalink / raw)
  To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>; kehan5800@gmail.com <kehan5800@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

On Mon, Nov 16, 2026 at 9:16 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> Hi Nisha,
>
> At high level the patch LGTM, a couple of minor comments:
>

Thanks for the review.

> 1.
> In RelationFindDeletedTupleInfoByIndex():
> {
> ...
> scanslot = table_slot_create(rel, NULL);
> idxrel = index_open(idxoid, RowExclusiveLock);
> if (!skipduplicates &&
> replindex_key_has_unsearchable_null(idxrel, searchslot))
> {
> index_close(idxrel, NoLock);
> ExecDropSingleTupleTableSlot(scanslot);
> return RelationFindDeletedTupleInfoSeq()
> }
> Moving `scanslot = table_slot_create(rel, NULL);` below the fallback check
> avoids allocating and immediately dropping the slot on every NULL-key
> fallback.
>

Agree, done.

> 2.
> In doc/src/sgml/logical-replication.sgml, in the section below[1] it
> may be worth briefly noting that "lookups for
> rows with NULL in a hash-indexed column fall back to a sequential scan." ?
>
> [1]
>     <para>
>      When using
>      <link linkend="sql-altertable-replica-identity-full"><literal>REPLICA
> IDENTITY FULL</literal></link>
>      on published tables, it is important to note that the
> <literal>UPDATE</literal>
>      and <literal>DELETE</literal> operations cannot be applied to subscribers
>      if the tables include attributes with datatypes (such as point or box)
>      that do not have a default operator class for B-tree or Hash. However,
>      this limitation can be overcome by ensuring that the table has a primary
>      key or replica identity defined for it.
>     </para>
>
>

IIUC, this para is under Restrictions, which lists cases where
UPDATE/DELETE cannot be applied. With this fix, rows with NULL in a
hash-indexed column are applied correctly, so I don't think there is a
restriction to document here.

Now, which scan finds a particular row seems like an internal detail,
and I don't see similar fallbacks documented elsewhere. [1] already
notes that the subscriber search can be very inefficient without a
suitable index, which I think is enough.
Thoughts?

Attached updated patch.

[1] https://www.postgresql.org/docs/devel/logical-replication-publication.html#LOGICAL-REPLICATION-PUBLI...

--
Thanks,
Nisha

Attachments:

v3-0001-Fix-lookup-of-rows-with-NULL-keys-through-a-hash-.patch (application/octet-stream, 10.4K) download From e464c5743a644f8fd1f2afddb159ac4d34ce2da1 Mon Sep 17 00:00:00 2001 From: Nisha Moond <nisha.moond412@gmail.com> Date: Fri, 13 Nov 2026 16:50:46 +0530 Subject: [PATCH v3] Fix lookup of rows with NULL keys through a hash index in logical replication. With REPLICA IDENTITY FULL, the apply worker can use a hash index on the subscriber to find the old row of an UPDATE or DELETE. Hash indexes don't store NULLs, so when the indexed column is NULL in the old row, the index scan finds nothing. The change was then skipped and reported as update_missing or delete_missing, leaving the subscriber out of sync. Fall back to a sequential scan in that case. Rows with non-NULL keys still use the index. Do the same when searching for a deleted row to detect update_deleted conflicts with retain_dead_tuples. --- src/backend/executor/execReplication.c | 52 +++++++++++- .../subscription/t/032_subscribe_use_index.pl | 80 +++++++++++++++++++ src/test/subscription/t/035_conflicts.pl | 67 ++++++++++++++++ 3 files changed, 197 insertions(+), 2 deletions(-) diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c index 66a788e9e3d..c242cfba460 100644 --- a/src/backend/executor/execReplication.c +++ b/src/backend/executor/execReplication.c @@ -175,6 +175,29 @@ should_refetch_tuple(TM_Result res, TM_FailureData *tmfd) return refetch; } +/* + * Return true if 'searchslot' has a NULL in a key column of 'idxrel' and the + * index can't search for NULLs (e.g. hash). Such a scan finds nothing. + */ +static bool +replindex_key_has_unsearchable_null(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. * @@ -207,6 +230,18 @@ RelationFindReplTupleByIndex(Relation rel, Oid idxoid, /* Open the index. */ idxrel = index_open(idxoid, RowExclusiveLock); + /* + * Fall back to a sequential scan if the index cannot search for a NULL + * key. !skipduplicates means the index was chosen for REPLICA IDENTITY + * FULL, so 'searchslot' has the whole row. + */ + if (!skipduplicates && + replindex_key_has_unsearchable_null(idxrel, searchslot)) + { + index_close(idxrel, NoLock); + return RelationFindReplTupleSeq(rel, lockmode, searchslot, outslot); + } + InitDirtySnapshot(snap); /* Build scan key. */ @@ -732,10 +767,23 @@ RelationFindDeletedTupleInfoByIndex(Relation rel, Oid idxoid, *delete_time = 0; *delete_origin = InvalidReplOriginId; - scanslot = table_slot_create(rel, NULL); - idxrel = index_open(idxoid, RowExclusiveLock); + /* + * Fall back to a sequential scan if the index cannot search for a NULL + * key. See RelationFindReplTupleByIndex(). + */ + if (!skipduplicates && + replindex_key_has_unsearchable_null(idxrel, searchslot)) + { + index_close(idxrel, NoLock); + return RelationFindDeletedTupleInfoSeq(rel, InvalidOid, searchslot, + oldestxmin, delete_xid, + delete_origin, delete_time); + } + + scanslot = table_slot_create(rel, NULL); + /* Build scan key. */ skey_attoff = build_replindex_scan_key(skey, rel, idxrel, searchslot); diff --git a/src/test/subscription/t/032_subscribe_use_index.pl b/src/test/subscription/t/032_subscribe_use_index.pl index bd80465f727..a5becaab3ff 100644 --- a/src/test/subscription/t/032_subscribe_use_index.pl +++ b/src/test/subscription/t/032_subscribe_use_index.pl @@ -606,6 +606,86 @@ $node_subscriber->safe_psql('postgres', "DROP TABLE test_replica_id_full"); # Testcase end: Subscription can use hash index # ============================================================================= +# ============================================================================= +# Testcase start: Subscription finds rows with NULL keys when using hash index +# +# Hash indexes do not store NULLs, so an index scan for a NULL key value finds +# nothing. UPDATE/DELETE of a row whose hash-indexed column is NULL must still +# be applied, rather than being reported as update_missing/delete_missing. + +# create table, initial data (including rows with NULL in the indexed column) +# and publication on pub +$node_publisher->safe_psql( + 'postgres', q[ + CREATE TABLE test_hash_null (x int, y text); + ALTER TABLE test_hash_null REPLICA IDENTITY FULL; + INSERT INTO test_hash_null VALUES + (1, 'one'), (2, 'two'), + (NULL, 'null-1'), (NULL, 'null-2'), (NULL, 'null-3'); + CREATE PUBLICATION tap_pub_hash_null FOR TABLE test_hash_null; +]); + +# create table, hash index and subscription on sub +$node_subscriber->safe_psql( + 'postgres', qq[ + CREATE TABLE test_hash_null (x int, y text); + CREATE INDEX test_hash_null_idx ON test_hash_null USING HASH (x); + CREATE SUBSCRIPTION tap_sub_hash_null CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub_hash_null; +]); + +# wait for initial table synchronization to finish +$node_subscriber->wait_for_subscription_sync($node_publisher, $appname); + +# update a row with a non-NULL key, and make sure the hash index is used +$node_publisher->safe_psql('postgres', + "UPDATE test_hash_null SET y = 'two updated' WHERE x = 2"); +$node_publisher->wait_for_catchup($appname); +$node_subscriber->poll_query_until('postgres', + q{select (idx_scan = 1) from pg_stat_all_indexes where indexrelname = 'test_hash_null_idx';} + ) + or die + "Timed out while waiting for check subscriber tap_sub_hash_null updates one row via index"; + +# update and delete rows with a NULL key, and set the key of a row with a +# NULL key to a non-NULL value +$node_publisher->safe_psql( + 'postgres', q[ + UPDATE test_hash_null SET y = 'null-1 updated' WHERE y = 'null-1'; + DELETE FROM test_hash_null WHERE y = 'null-2'; + UPDATE test_hash_null SET x = 3 WHERE y = 'null-3'; +]); +$node_publisher->wait_for_catchup($appname); + +# make sure that the subscriber has the same data as the publisher +my $pub_result = $node_publisher->safe_psql('postgres', + "SELECT x, y FROM test_hash_null ORDER BY y"); +$result = $node_subscriber->safe_psql('postgres', + "SELECT x, y FROM test_hash_null ORDER BY y"); +is($result, $pub_result, + 'ensure subscriber applies UPDATE/DELETE of rows with NULL in hash-indexed column' +); +is( $result, qq(|null-1 updated +3|null-3 +1|one +2|two updated), + 'ensure subscriber has the correct data at the end of the test'); + +# cleanup pub +$node_publisher->safe_psql( + 'postgres', q[ + DROP PUBLICATION tap_pub_hash_null; + DROP TABLE test_hash_null; +]); +# cleanup sub +$node_subscriber->safe_psql( + 'postgres', q[ + DROP SUBSCRIPTION tap_sub_hash_null; + DROP TABLE test_hash_null; +]); + +# Testcase end: Subscription finds rows with NULL keys when using hash index +# ============================================================================= + # ============================================================================= # Testcase start: Subscription keeps using an index that concurrent DDL has # demoted from replica identity diff --git a/src/test/subscription/t/035_conflicts.pl b/src/test/subscription/t/035_conflicts.pl index 48ce95d9d13..c133a08d530 100644 --- a/src/test/subscription/t/035_conflicts.pl +++ b/src/test/subscription/t/035_conflicts.pl @@ -525,6 +525,73 @@ $node_A->safe_psql('postgres', $node_A->safe_psql('postgres', "DROP TABLE tab_mr"); $node_B->safe_psql('postgres', "DROP TABLE tab_mr"); +############################################################################### +# Check that the search for a deleted tuple finds a row whose key is NULL in a +# hash index chosen for REPLICA IDENTITY FULL. Hash indexes do not store NULLs, +# so the row must be found without the index scan, and the conflict reported +# as update_deleted instead of update_missing. +############################################################################### + +# Publisher table holding the row that will be updated. +$node_B->safe_psql( + 'postgres', " + CREATE TABLE tab_hash_null (a int, b text); + ALTER TABLE tab_hash_null REPLICA IDENTITY FULL; + INSERT INTO tab_hash_null VALUES (NULL, 'null-1'); + ALTER PUBLICATION tap_pub_B ADD TABLE tab_hash_null; +"); + +# Subscriber table with the hash index. The index is created before the +# conflict detection slot's xmin is pinned below, so that it can be used to +# find deleted tuples. +$node_A->safe_psql( + 'postgres', " + CREATE TABLE tab_hash_null (a int, b text); + CREATE INDEX tab_hash_null_idx ON tab_hash_null USING HASH (a); + ALTER SUBSCRIPTION $subname_AB REFRESH PUBLICATION; +"); +$node_A->wait_for_subscription_sync($node_B, $subname_AB); + +# Pin the conflict detection slot's xmin, as above. +$holder = $node_A->background_psql('postgres'); +$holder->query_safe('BEGIN'); +$pinned_xid = $holder->query_safe('SELECT txid_current()'); + +ok( $node_A->poll_query_until( + 'postgres', + "SELECT xmin = $pinned_xid FROM pg_replication_slots WHERE slot_name = 'pg_conflict_detection'" + ), + "conflict detection slot xmin pinned at $pinned_xid"); + +# Delete the row locally, then update it on the publisher. +$node_A->safe_psql('postgres', + "DELETE FROM tab_hash_null WHERE b = 'null-1'"); + +$log_location = -s $node_A->logfile; + +$node_B->safe_psql('postgres', + "UPDATE tab_hash_null SET b = 'null-1 updated' WHERE b = 'null-1'"); + +$node_B->wait_for_catchup($subname_AB); + +$logfile = slurp_file($node_A->logfile(), $log_location); +like( + $logfile, + qr/conflict detected on relation "public.tab_hash_null": conflict=update_deleted.* +.*DETAIL:.* Could not find the row to be updated: remote row \(null, null-1 updated\), replica identity full \(null, null-1\). +.*The row to be updated was deleted locally in transaction [0-9]+ at .*/, + 'update target row with NULL key was deleted in tab_hash_null'); + +# Clean up +$holder->query_safe('COMMIT'); +ok($holder->quit, 'release the pinned transaction'); +$node_B->safe_psql('postgres', + "ALTER PUBLICATION tap_pub_B DROP TABLE tab_hash_null"); +$node_A->safe_psql('postgres', + "ALTER SUBSCRIPTION $subname_AB REFRESH PUBLICATION"); +$node_A->safe_psql('postgres', "DROP TABLE tab_hash_null"); +$node_B->safe_psql('postgres', "DROP TABLE tab_hash_null"); + ############################################################################### # Check that the xmin value of the conflict detection slot can be advanced when # the subscription has no tables. -- 2.54.0 (Apple Git-157)
^ permalink raw reply [nested|flat] 8+ messages in thread


end of thread, other threads:[~2026-11-17 15:32 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-11 22:19 BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column PG Bug reporting form <noreply@postgresql.org>
2026-11-13 11:32 ` Nisha Moond <nisha.moond412@gmail.com>
2026-11-16 08:01   ` Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
2026-11-16 12:19     ` Nisha Moond <nisha.moond412@gmail.com>
2026-11-16 13:08       ` Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
2026-11-16 15:15         ` Nisha Moond <nisha.moond412@gmail.com>
2026-11-16 15:46           ` Dilip Kumar <dilipbalaut@gmail.com>
2026-11-17 15:32             ` Nisha Moond <nisha.moond412@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox