Re: row filtering for logical replication

On Mon, Nov 29, 2021 at 3:41 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> > ---- Publisher:
> > INSERT INTO tbl1 VALUES (1,1);
> > UPDATE tbl1 SET a = 2;
> >
> > Prior to the UPDATE above:
> > On pub side, tbl1 contains (1,1).
> > On sub side, tbl1 contains (1,1)
> >
> > After the above UPDATE:
> > On pub side, tbl1 contains (2,1).
> > On sub side, tbl1 contains (1,1), (2,1)
> >
> > So the UPDATE on the pub side has resulted in an INSERT of (2,1) on
> > the sub side.
> >
> > This is because when (1,1) is UPDATEd to (2,1), it attempts to use the
> > "insert" filter "(b<2)" to determine whether the old value had been
> > inserted (published to subscriber), but finds there is no "b" value
> > (because it only uses RI cols for UPDATE) and so has to assume the old
> > tuple doesn't exist on the subscriber, hence the UPDATE ends up doing
> > an INSERT.
> > INow if the use of RI cols were enforced for the insert filter case,
> > we'd properly know the answer as to whether the old row value had been
> > published and it would have correctly performed an UPDATE instead of
> > an INSERT in this case.
> >
>
> I don't think it is a good idea to combine the row-filter from the
> publication that publishes just 'insert' with the row-filter that
> publishes 'updates'. We shouldn't apply the 'insert' filter for
> 'update' and similarly for publication operations. We can combine the
> filters when the published operations are the same. So, this means
> that we might need to cache multiple row-filters but I think that is
> better than having another restriction that publish operation 'insert'
> should also honor RI columns restriction.

I am just wondering that if we don't combine filter in the above case
then what data we will send to the subscriber if the operation is
"UPDATE tbl1 SET a = 2, b=3", so in this case, we will apply only the
update filter i.e. a > 1 so as per that this will become the INSERT
operation because the old row was not passing the filter. So now we
will insert a new row in the subscriber-side with value (2,3). Looks
a bit odd to me that the value b=3 would have been rejected with the
direct insert but it is allowed due to indirect insert done by update.
Is this behavior looks odd only to me?

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com