On Mon, Nov 29, 2021 at 4:36 PM Dilip Kumar <dilipbal...@gmail.com> wrote: > > On Mon, Nov 29, 2021 at 3:41 PM Amit Kapila <amit.kapil...@gmail.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. >
If we want, I think for inserts (new row) we can consider the insert filter as well but that makes it tricky to explain. I feel we can change it later as well if there is a valid use case for this. What do you think? -- With Regards, Amit Kapila.