Horton

Re: row filtering for logical replication

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: Peter Smith <smithpb2250@gmail.com>
Cc: "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, vignesh C <vignesh21@gmail.com>, Greg Nancarrow <gregn4422@gmail.com>, "tanghy.fnst@fujitsu.com" <tanghy.fnst@fujitsu.com>, Ajin Cherian <itsajin@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, Rahila Syed <rahilasyed90@gmail.com>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Önder Kalacı <onderkalaci@gmail.com>, japin <japinli@hotmail.com>, Michael Paquier <michael@paquier.xyz>, David Steele <david@pgmasters.net>, Craig Ringer <craig@2ndquadrant.com>, Amit Langote <amitlangote09@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Euler Taveira <euler@eulerto.com>
Date: 2021-11-26T06:45:17Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Release cache tuple when no longer needed

  2. Add some additional tests for row filters in logical replication.

  3. Fix one of the tests introduced in commit 52e4f0cd47.

  4. Allow specifying row filters for logical replication of tables.

  5. Move scanint8() to numutils.c

  6. Replace Test::More plans with done_testing

  7. Reduce relcache access in WAL sender streaming logical changes

  8. Small cleanups related to PUBLICATION framework code

  9. Add a view to show the stats of subscription workers.

  10. Allow publishing the tables of schema.

  11. Doc: improve documentation of CREATE/ALTER SUBSCRIPTION.

  12. Add PublicationTable and PublicationRelInfo structs

  13. Remove unused argument "txn" in maybe_send_schema().

  14. Add prepare API support for streaming transactions in logical replication.

  15. Unify PostgresNode's new() and get_new_node() methods

  16. Use l*_node() family of functions where appropriate

  17. Add support for prepared transactions to built-in logical replication.

  18. Restore the portal-level snapshot after procedure COMMIT/ROLLBACK.

  19. Rename a parse node to be more general

  20. Remove unused column atttypmod from initial tablesync query

  21. SEARCH and CYCLE clauses

On Fri, Nov 26, 2021 at 12:01 PM Peter Smith <smithpb2250@gmail.com> wrote:
>
> On Fri, Nov 26, 2021 at 4:18 PM Peter Smith <smithpb2250@gmail.com> wrote:
> >
> > > > Do you mean to say that we should give an error on Update/Delete if any of the
> > > > publications contain table rowfilter that has columns that are not part of the
> > > > primary key or replica identity? I think this is what Hou-san has implemented in
> > > > his top-up patch and I also think this is the right behavior.
> > >
> > > Yes, the top-up patch will give an error if the columns in row filter are not part of
> > > replica identity when UPDATE and DELETE.
> > >
> > > But the point I want to confirm is that:
> > >

Okay, I see your point now.

> > > ---
> > > create publication A for table tbl1 where (b<2) with(publish='insert');
> > > create publication B for table tbl1 where (a>1) with(publish='update');
> > > ---
> > >
> > > When UPDATE on the table 'tbl1', is it correct to combine and execute both of
> > > the row filter in A(b<2) and B(a>1) ?(it's the current behavior)
> > >
> > > Because the filter in A has an unlogged column(b) and the publication A only
> > > publish "insert", so for UPDATE, should we skip the row filter in A and only
> > > execute the row filter in B ?
> > >
> >
> > But since the filters are OR'ed together does it even matter?
> >

Even if it is OR'ed, if the value is not logged (as it was not part of
replica identity or primary key) as per Hou-San's example, how will
evaluate such a filter?

> > Now that your top-up patch now prevents invalid updates/deletes, this
> > other point is only really a question about the cache performance,
> > isn't it?
> >
>
> Irrespective of replica identity I think there is still a functional
> behaviour question, right?
>
> e.g.
> create publication p1 for table census where (country = 'Aust') with
> (publish="update")
> create publication p2 for table census where (country = 'NZ') with
> (publish='insert')
>
> Should it be possible to UPDATE for country 'NZ' or not?
> Is this the same as your question Hou-san?
>

I am not sure if it is the same because in Hou-San's example
publications refer to different columns where one of the columns was
part of PK and another was not whereas in your example both refer to
the same column. I think in your example the error will happen at the
time of update/delete whereas in Hou-San's example it won't happen at
the time of update/delete.

With Regards,
Amit Kapila.