Horton

Re: row filtering for logical replication

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: Dilip Kumar <dilipbalaut@gmail.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>, Euler Taveira <euler@eulerto.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Peter Smith <smithpb2250@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>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Amit Langote <amitlangote09@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2021-07-16T05:49:53Z
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, Jul 16, 2021 at 10:11 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Fri, Jul 16, 2021 at 8:57 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > On Wed, Jul 14, 2021 at 4:30 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> > >
> > > On Wed, Jul 14, 2021 at 3:58 PM Tomas Vondra
> > > <tomas.vondra@enterprisedb.com> wrote:
> > > >
> > > > Is there some reasonable rule which of the old/new tuples (or both) to
> > > > use for the WHERE condition? Or maybe it'd be handy to allow referencing
> > > > OLD/NEW as in triggers?
> > >
> > > I think for insert we are only allowing those rows to replicate which
> > > are matching filter conditions, so if we updating any row then also we
> > > should maintain that sanity right? That means at least on the NEW rows
> > > we should apply the filter, IMHO.  Said that, now if there is any row
> > > inserted which were satisfying the filter and replicated, if we update
> > > it with the new value which is not satisfying the filter then it will
> > > not be replicated,  I think that makes sense because if an insert is
> > > not sending any row to a replica which is not satisfying the filter
> > > then why update has to do that, right?
> > >
> >
> > There is another theory in this regard which is what if the old row
> > (created by the previous insert) is not sent to the subscriber as that
> > didn't match the filter but after the update, we decide to send it
> > because the updated row (new row) matches the filter condition. In
> > this case, I think it will generate an update conflict on the
> > subscriber as the old row won't be present. As of now, we just skip
> > the update but in the future, we might have some conflict handling
> > there. If this is true then even if the new row matches the filter,
> > there is no guarantee that it will be applied on the subscriber-side
> > unless the old row also matches the filter.
>
> Yeah, it's a valid point.
>
>  Sure, there could be a
> > case where the user might have changed the filter between insert and
> > update but maybe we can have a separate way to deal with such cases if
> > required like providing some provision where the user can specify
> > whether it would like to match old/new row in updates?
>
> Yeah, I think the best way is that users should get an option whether
> they want to apply the filter on the old row or on the new row, or
> both, in fact, they should be able to apply the different filters on
> old and new rows.
>

I am not so sure about different filters for old and new rows but it
makes sense to by default apply the filter to both old and new rows.
Then also provide a way for user to specify if the filter can be
specified to just old or new row.

>  I have one more thought in mind: currently, we are
> providing a filter for the publication table, doesn't it make sense to
> provide filters for operations of the publication table?  I mean the
> different filters for Insert, delete, and the old row of update and
> the new row of the update.
>

Hmm, I think this sounds a bit of a stretch but if there is any field
use case then we can consider this in the future.

-- 
With Regards,
Amit Kapila.