Re: row filtering for logical replication

On Tue, Nov 30, 2021 at 9:34 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
...
> Thanks for the updated patch, few comments:
> 1) Should this be changed to include non IMMUTABLE system functions
> are not allowed:
> + not-null constraints in the <literal>WHERE</literal> clause. The
> + <literal>WHERE</literal> clause does not allow functions or user-defined
> + operators.
> + </para>
>

Updated docs in v44 [1]

> 2) We can remove the #if 0 code if we don't plan to keep it in the final patch.
> --- a/src/backend/parser/parse_agg.c
> +++ b/src/backend/parser/parse_agg.c
> @@ -552,11 +552,12 @@ check_agglevels_and_constraints(ParseState
> *pstate, Node *expr)
>
> break;
> case EXPR_KIND_PUBLICATION_WHERE:
> +#if 0
> if (isAgg)
> err = _("aggregate functions are not
> allowed in publication WHERE expressions");
> else
> err = _("grouping operations are not
> allowed in publication WHERE expressions");
> -
> +#endif
>

Fixed in v44 [1]

> 4) Should this be changed, since we error out if publisher without
> replica identify performs delete or update:
> + The <literal>WHERE</literal> clause must contain only columns that are
> + covered by <literal>REPLICA IDENTITY</literal>, or are part of the primary
> + key (when <literal>REPLICA IDENTITY</literal> is not set), otherwise
> + <command>DELETE</command> or <command>UPDATE</command> operations will not
> + be replicated. That's because old row is used and it only contains primary
> + key or columns that are part of the <literal>REPLICA IDENTITY</literal>; the
> + remaining columns are <literal>NULL</literal>. For <command>INSERT</command>
>
> to:
> + The <literal>WHERE</literal> clause must contain only columns that are
> + covered by <literal>REPLICA IDENTITY</literal>, or are part of the primary
> + key (when <literal>REPLICA IDENTITY</literal> is not set), otherwise
> + <command>DELETE</command> or <command>UPDATE</command> operations will be
> + disallowed on those tables. That's because old row is used and it
> only contains primary
> + key or columns that are part of the <literal>REPLICA IDENTITY</literal>; the
> + remaining columns are <literal>NULL</literal>. For <command>INSERT</command>
>

Updated docs in v44 [1]

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

Kind Regards,
Peter Smith.
Fujitsu Australia