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