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

From: Dilip Kumar <dilipbalaut(at)gmail(dot)com>
To: Nisha Moond <nisha(dot)moond412(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-16 15:46:23
Message-ID: CAFiTN-utD9Qs0p4AA8cioz5rBs24c9k-4JiG-iNQARR0G-yn2Q@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Mon, Nov 16, 2026 at 8:46 PM Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> wrote:
>
> On Mon, Nov 16, 2026 at 6:38 PM Hayato Kuroda (Fujitsu)
> <kuroda(dot)hayato(at)fujitsu(dot)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

In response to

Browse pgsql-bugs by date

  From Date Subject
Previous Message Nisha Moond 2026-11-16 15:15:40 Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column