Horton

Re: row filtering for logical replication

Peter Smith <smithpb2250@gmail.com>

View thread
From: Peter Smith <smithpb2250@gmail.com>
To: Euler Taveira <euler@eulerto.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, Amit Kapila <amit.kapila16@gmail.com>, Ajin Cherian <itsajin@gmail.com>, "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, Greg Nancarrow <gregn4422@gmail.com>, vignesh C <vignesh21@gmail.com>, "tanghy.fnst@fujitsu.com" <tanghy.fnst@fujitsu.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>
Date: 2022-01-04T04:55:11Z
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 Mon, Dec 27, 2021 at 9:57 AM Euler Taveira <euler@eulerto.com> wrote:
>
> On Sun, Dec 26, 2021, at 1:09 PM, Alvaro Herrera wrote:
>
> On 2021-Dec-26, Euler Taveira wrote:
>
> > On Sat, Dec 25, 2021, at 1:20 AM, Amit Kapila wrote:
> > > On Fri, Dec 24, 2021 at 11:04 AM Peter Smith <smithpb2250@gmail.com> wrote:
> > > >
> > > > So, IMO the PG docs wording for this part should be relaxed a bit.
> > > >
> > > > e.g.
> > > > BEFORE:
> > > > +   A nullable column in the <literal>WHERE</literal> clause could cause the
> > > > +   expression to evaluate to false; avoid using columns without not-null
> > > > +   constraints in the <literal>WHERE</literal> clause.
> > > > AFTER:
> > > > +   A nullable column in the <literal>WHERE</literal> clause could cause the
> > > > +   expression to evaluate to false. To avoid unexpected results, any possible
> > > > +   null values should be accounted for.
>
> Is this actually correct?  I think a null value would cause the
> expression to evaluate to null, not false; the issue is that the filter
> considers a null value as not matching (right?).  Maybe it's better to
> spell that out explicitly; both these wordings seem distracting.
>
> [Reading it again...] I think it is referring to the
> pgoutput_row_filter_exec_expr() return. That's not accurate because it is
> talking about the expression and the expression returns true, false and null.
> However, the referred function returns only true or false. I agree that we
> should explictily mention that a null return means the row won't be published.
>
> You have this elsewhere:
>
> +      If the optional <literal>WHERE</literal> clause is specified, only rows
> +      that satisfy the <replaceable class="parameter">expression</replaceable>
> +      will be published. Note that parentheses are required around the
> +      expression. It has no effect on <literal>TRUNCATE</literal> commands.
>
> Maybe this whole thing is clearer if you just say "If the optional WHERE
> clause is specified, rows for which the expression returns false or null
> will not be published."  With that it should be fairly clear what
> happens if you have NULL values in the columns used in the expression,
> and you can just delete that phrase you're discussing.
>
> Your proposal sounds good to me.

Modified as suggested in v58 [1].

------
[1] https://www.postgresql.org/message-id/CAHut%2BPvkswkGLqzYo7z9rwOoDeLtUk0PEha8kppNvZts0h22Hw%40mail.gmail.com

Kind Regards,
Peter Smith.
Fujitsu Australia