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: Greg Nancarrow <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Ajin Cherian <[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: Thu, 25 Nov 2021 13:39:45 +0000
Message-ID: <OS0PR01MB57168FD9932E3F42406EB13B94629@OS0PR01MB5716.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <CAA4eK1KVUs2s3PTcTTRj3qdmUzEDG7PQ5Xq_b1J7zMsi2OvE5w@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>
On Wed, Nov 24, 2021 1:46 PM Amit Kapila <[email protected]> wrote:
> On Wed, Nov 24, 2021 at 6:51 AM [email protected]
> <[email protected]> wrote:
> >
> > On Tues, Nov 23, 2021 6:16 PM Amit Kapila <[email protected]> wrote:
> > > On Tue, Nov 23, 2021 at 1:29 PM [email protected]
> > > <[email protected]> wrote:
> > > >
> > > > On Tues, Nov 23, 2021 2:27 PM vignesh C <[email protected]> wrote:
> > > > > On Thu, Nov 18, 2021 at 7:04 AM Peter Smith
> > > > > <[email protected]>
> > > > > wrote:
> > > > > >
> > > > > > PSA new set of v40* patches.
> > > > >
> > > > > Few comments:
> > > > > 1) When a table is added to the publication, replica identity is
> > > > > checked. But while modifying the publish action to include
> > > > > delete/update, replica identity is not checked for the existing
> > > > > tables. I felt it should be checked for the existing tables too.
> > > >
> > > > In addition to this, I think we might also need some check to
> > > > prevent user from changing the REPLICA IDENTITY index which is used in
> > > > the filter expression.
> > > >
> > > > I was thinking is it possible do the check related to REPLICA
> > > > IDENTITY in function CheckCmdReplicaIdentity() or In
> > > > GetRelationPublicationActions(). If we move the REPLICA IDENTITY
> > > > check to this function, it would be consistent with the existing
> > > > behavior about the check related to REPLICA IDENTITY(see the
> > > > comments in CheckCmdReplicaIdentity) and seems can cover all the cases
> > > > mentioned above.
> > >
> > > Yeah, adding the replica identity check in CheckCmdReplicaIdentity()
> > > would cover all the above cases but I think that would put a premium
> > > on each update/delete operation. I think traversing the expression
> > > tree (it could be multiple traversals if the relation is part of
> > > multiple publications) during each update/delete would be costly.
> > > Don't you think so?
> >
> > Yes, I agreed that traversing the expression every time would be costly.
> >
> > I thought maybe we can cache the columns used in row filter or cache
> > only the a
> > flag(can_update|delete) in the relcache. I think every operation that
> > affect the row-filter or replica-identity will invalidate the relcache
> > and the cost of check seems acceptable with the cache.
> >
>
> I think if we can cache this information especially as a bool flag then that should
> probably be better.
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)
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: <OS0PR01MB57168FD9932E3F42406EB13B94629@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