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

Поиск
Список
Период
Сортировка
Искать

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

От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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.

--- 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.




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

От:
Nisha Moond <nisha.moond412@gmail.com>
Дата:
Вложения:
On Thu, Nov 12, 2026 at 10:52 PM PG Bug reporting form
 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

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>
Дата:
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

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

От:
Nisha Moond <nisha.moond412@gmail.com>
Дата:
On Mon, Nov 16, 2026 at 1:31 PM Hayato Kuroda (Fujitsu)
 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


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>
Дата:
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

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

От:
Nisha Moond <nisha.moond412@gmail.com>
Дата:
Вложения:
 On Mon, Nov 16, 2026 at 6:38 PM Hayato Kuroda (Fujitsu)
 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.jpnprd01.prod.outlook.com
--
Thanks,
Nisha

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

От:
Dilip Kumar <dilipbalaut@gmail.com>
Дата:
On Mon, Nov 16, 2026 at 8:46 PM Nisha Moond  wrote:
>
>  On Mon, Nov 16, 2026 at 6:38 PM Hayato Kuroda (Fujitsu)
>  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]
    
     When using
     REPLICA
IDENTITY FULL
     on published tables, it is important to note that the
UPDATE
     and DELETE 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.
    


-- 
Regards,
Dilip Kumar
Google


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

От:
Nisha Moond <nisha.moond412@gmail.com>
Дата:
Вложения:
On Mon, Nov 16, 2026 at 9:16 PM Dilip Kumar  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]
>     
>      When using
>      REPLICA
> IDENTITY FULL
>      on published tables, it is important to note that the
> UPDATE
>      and DELETE 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.
>     
>
>

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-PUBLICATION-REPLICA-IDENTITY

--
Thanks,
Nisha
FAQ