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

From: Nisha Moond <nisha(dot)moond412(at)gmail(dot)com>
To: Dilip Kumar <dilipbalaut(at)gmail(dot)com>
Cc: "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com>, "kehan5800(at)gmail(dot)com" <kehan5800(at)gmail(dot)com>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column
Date: 2026-11-17 15:32:59
Message-ID: CABdArM5r0orXz1qk-EkqjjLV_+N1kPnB4X443rAWsFnNH94EJA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Mon, Nov 16, 2026 at 9:16 PM Dilip Kumar <dilipbalaut(at)gmail(dot)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-PUBLICATION-REPLICA-IDENTITY

--
Thanks,
Nisha

Attachment Content-Type Size
v3-0001-Fix-lookup-of-rows-with-NULL-keys-through-a-hash-.patch application/octet-stream 10.4 KB

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2026-11-17 15:41:01 Re: BUG #19910: json(b)_to_tsvector() nested array filter gives internal error XX000 "unexpected end of flag array"
Previous Message PG Bug reporting form 2026-11-17 15:01:50 BUG #19913: Parent ALTER TABLE during concurrent DETACH leaves pending partition unusable (42804)