Re: row filtering for logical replication

On Fri, Dec 17, 2021 at 5:46 PM Greg Nancarrow <gregn4422@gmail.com> wrote:

> So using the v47 patch-set, I still find that the UPDATE above results in publication of an INSERT of (2,1), rather
thanan UPDATE of (1,1) to (2,1). 
> This is according to the 2nd UPDATE rule below, from patch 0003.
>
> + * old-row (no match)    new-row (no match)  -> (drop change)
> + * old-row (no match)    new row (match)     -> INSERT
> + * old-row (match)       new-row (no match)  -> DELETE
> + * old-row (match)       new row (match)     -> UPDATE
>
> This is because the old row (1,1) doesn't match the UPDATE filter "(a>1)", but the new row (2,1) does.
> This functionality doesn't seem right to me. I don't think it can be assumed that (1,1) was never published (and thus
requiresan INSERT rather than UPDATE) based on these checks, because in this example, (1,1) was previously published
viaa different operation - INSERT (and using a different filter too). 
> I think the fundamental problem here is that these UPDATE rules assume that the old (current) row was previously
UPDATEd(and published, or not published, according to the filter applicable to UPDATE), but this is not necessarily the
case.
> Or am I missing something?

But it need not be correct in assuming that the old-row was part of a
previous INSERT either (and published, or not published according to
the filter applicable to an INSERT).
For example, change the sequence of inserts and updates prior to the
last update:

truncate tbl1 ;
insert into tbl1 values (1,5); ==> not replicated since insert and ! (b < 2);
update tbl1 set b = 1; ==> not replicated since update and ! (a > 1)
update tbl1 set a = 2; ==> replicated and update converted to insert
since (a > 1)

In this case, the last update "update tbl1 set a = 2; " is updating a
row that was previously updated and not inserted and not replicated to
the subscriber.
How does the replication logic differentiate between these two cases,
and decide if the update was previously published or not?
I think it's futile for the publisher side to try and figure out the
history of published rows. In fact, if this level of logic is required
then it is best implemented on the subscriber side, which then defeats
the purpose of a publication filter.


regards,
Ajin Cherian
Fujitsu Australia