public inbox for [email protected]
help / color / mirror / Atom feedFrom: [email protected] <[email protected]>
To: Amit Kapila <[email protected]>
Cc: vignesh C <[email protected]>
Cc: Peter Smith <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Ajin Cherian <[email protected]>
Cc: Greg Nancarrow <[email protected]>
Cc: Dilip Kumar <[email protected]>
Cc: Euler Taveira <[email protected]>
Cc: Rahila Syed <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Önder Kalacı <[email protected]>
Cc: japin <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: David Steele <[email protected]>
Cc: Craig Ringer <[email protected]>
Cc: Amit Langote <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: RE: row filtering for logical replication
Date: Tue, 30 Nov 2021 01:39:05 +0000
Message-ID: <OS0PR01MB57166F44723AB76866F74A0694679@OS0PR01MB5716.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <CAA4eK1+8UqT5b3Ha42Wt07668yxs9KkKvV5r==vT9KfRUGCs-g@mail.gmail.com>
References: <CAHut+PvSHjUsrrgVeE43hN8AZVPa8VqQ=GH1LYJFSubp2W_dhA@mail.gmail.com>
<CAHut+PtRdXzPpm3qv3cEYWWfVUkGT84EopEHxwt95eo_cG_3eQ@mail.gmail.com>
<OS0PR01MB571625D4A5CC1DAB4045B2BB94919@OS0PR01MB5716.jpnprd01.prod.outlook.com>
<CAHut+Pu1AMDUwBNKeevLKOO2=M6ZBXpddi-s7fhqBpMxMWUQpA@mail.gmail.com>
<CAFPTHDZgxAbLA98HZkN-JNHoHXSBGtK4eAGQrXam5JQY6Zx7wg@mail.gmail.com>
<OS0PR01MB61132C0E4FFEE73D34AE9823FB999@OS0PR01MB6113.jpnprd01.prod.outlook.com>
<CAJcOf-d0=Pnv8qpBaN5WM5wtmhT3EJ64crWtfPBtiwtK8iUEwg@mail.gmail.com>
<CAHut+Pv-D4rQseRO_OzfEz2dQsTKEnKjBCET9Z-iJppyT1XNMQ@mail.gmail.com>
<CALDaNm2bq-Zab3i5pvuA3UTxHvo3BqPwmgXbyznpw5vz4=fxpA@mail.gmail.com>
<OS0PR01MB57162EB465A0E6BCFDF9B3F394609@OS0PR01MB5716.jpnprd01.prod.outlook.com>
<CAA4eK1KZ+N=vfgqeUuZfRLRC+WyNBFvU9Ei7kF-7OPXnTX2WrA@mail.gmail.com>
<OS0PR01MB5716F2C3904CB807050010A394619@OS0PR01MB5716.jpnprd01.prod.outlook.com>
<CAA4eK1KVUs2s3PTcTTRj3qdmUzEDG7PQ5Xq_b1J7zMsi2OvE5w@mail.gmail.com>
<OS0PR01MB57168FD9932E3F42406EB13B94629@OS0PR01MB5716.jpnprd01.prod.outlook.com>
<CAJcOf-dz0srExG0NPPgXh5X8eL2uxk7C=cZoGTbf8cNqoRUY6w@mail.gmail.com>
<CAA4eK1+8UqT5b3Ha42Wt07668yxs9KkKvV5r==vT9KfRUGCs-g@mail.gmail.com>
On Mon, Nov 29, 2021 6:11 PM Amit Kapila <[email protected]> wrote:
> On Mon, Nov 29, 2021 at 12:10 PM Greg Nancarrow <[email protected]>
> wrote:
> >
> > On Fri, Nov 26, 2021 at 12:40 AM [email protected]
> > <[email protected]> wrote:
> > >
> > > When researching and writing a top-up patch about this.
> > > I found a possible issue which I'd like to confirm first.
> > >
> > > It's possible the table is published in two publications A and B,
> > > publication A only publish "insert" , publication B publish
> > > "update". When UPDATE, both row filter in A and B will be executed. Is this
> behavior expected?
> > >
> > > For example:
> > > ---- Publication
> > > create table tbl1 (a int primary key, b int); create publication A
> > > for table tbl1 where (b<2) with(publish='insert'); create
> > > publication B for table tbl1 where (a>1) with(publish='update');
> > >
> > > ---- Subscription
> > > create table tbl1 (a int primary key); CREATE SUBSCRIPTION sub
> > > CONNECTION 'dbname=postgres host=localhost port=10000'
> PUBLICATION
> > > A,B;
> > >
> > > ---- Publication
> > > update tbl1 set a = 2;
> > >
> > > The publication can be created, and when UPDATE, the rowfilter in A
> > > (b<2) will also been executed but the column in it is not part of replica
> identity.
> > > (I am not against this behavior just confirm)
> > >
> >
> > There seems to be problems related to allowing the row filter to
> > include columns that are not part of the replica identity (in the case
> > of publish=insert).
> > In your example scenario, the tbl1 WHERE clause "(b < 2)" for
> > publication A, that publishes inserts only, causes a problem, because
> > column "b" is not part of the replica identity.
> > To see this, follow the simple example below:
> > (and note, for the Subscription, the provided tbl1 definition has an
> > error, it should also include the 2nd column "b int", same as in the
> > publisher)
> >
> > ---- 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.
Personally, I agreed that an UPDATE operation should only apply a row filter that
is part of a publication that has only UPDATE.
Best regards,
Hou zj
view thread (489+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: RE: row filtering for logical replication
In-Reply-To: <OS0PR01MB57166F44723AB76866F74A0694679@OS0PR01MB5716.jpnprd01.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox