Re: row filtering for logical replication

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