Re: row filtering for logical replication

On Wed, Jul 14, 2021 at 4:30 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Wed, Jul 14, 2021 at 3:58 PM Tomas Vondra
> <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> >
> > Is there some reasonable rule which of the old/new tuples (or both) to
> > use for the WHERE condition? Or maybe it'd be handy to allow referencing
> > OLD/NEW as in triggers?
>
> I think for insert we are only allowing those rows to replicate which
> are matching filter conditions, so if we updating any row then also we
> should maintain that sanity right? That means at least on the NEW rows
> we should apply the filter, IMHO. Said that, now if there is any row
> inserted which were satisfying the filter and replicated, if we update
> it with the new value which is not satisfying the filter then it will
> not be replicated, I think that makes sense because if an insert is
> not sending any row to a replica which is not satisfying the filter
> then why update has to do that, right?
>

There is another theory in this regard which is what if the old row
(created by the previous insert) is not sent to the subscriber as that
didn't match the filter but after the update, we decide to send it
because the updated row (new row) matches the filter condition. In
this case, I think it will generate an update conflict on the
subscriber as the old row won't be present. As of now, we just skip
the update but in the future, we might have some conflict handling
there. If this is true then even if the new row matches the filter,
there is no guarantee that it will be applied on the subscriber-side
unless the old row also matches the filter. Sure, there could be a
case where the user might have changed the filter between insert and
update but maybe we can have a separate way to deal with such cases if
required like providing some provision where the user can specify
whether it would like to match old/new row in updates?

--
With Regards,
Amit Kapila.