On Fri, Dec 17, 2021 at 1:50 PM Ajin Cherian <itsa...@gmail.com> wrote: > > On Fri, Dec 17, 2021 at 5:46 PM Greg Nancarrow <gregn4...@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 than an 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 requires an INSERT rather > > than UPDATE) based on these checks, because in this example, (1,1) was > > previously published via a 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. >
I also think so. One more thing, even if we want we might not be able to apply the insert filter as the corresponding values may not be logged. -- With Regards, Amit Kapila.