Re: row filtering for logical replication

On Tue, Jan 11, 2022 at 1:32 PM tanghy(dot)fnst(at)fujitsu(dot)com
<tanghy(dot)fnst(at)fujitsu(dot)com> wrote:
>
> On Tuesday, January 11, 2022 10:16 AM houzj(dot)fnst(at)fujitsu(dot)com <houzj(dot)fnst(at)fujitsu(dot)com> wrote:
> >
> > Attach the v62 patch set which address the above comments and slightly
> > adjust the commit message in 0002 patch.
> >
>
> I saw a possible problem about Row-Filter tablesync SQL, which is related
> to partition table.
>
> If a parent table is published with publish_via_partition_root off, its child
> table should be taken as no row filter when combining the row filters with OR.
> But when using the current SQL, this publication is ignored.
>
> For example:
> create table parent (a int) partition by range (a);
> create table child partition of parent default;
> create publication puba for table parent with (publish_via_partition_root=false);
> create publication pubb for table child where(a>10);
>
> Using current SQL in patch:
> (table child oid is 16387)
> SELECT DISTINCT pg_get_expr(prqual, prrelid) FROM pg_publication p
> INNER JOIN pg_publication_rel pr ON (p.oid = pr.prpubid)
> WHERE pr.prrelid = 16387 AND p.pubname IN ( 'puba', 'pubb' )
> AND NOT (select bool_or(puballtables)
> FROM pg_publication
> WHERE pubname in ( 'puba', 'pubb' ))
> AND NOT EXISTS (SELECT 1
> FROM pg_publication_namespace pn, pg_class c, pg_publication p
> WHERE c.oid = 16387 AND c.relnamespace = pn.pnnspid AND p.oid = pn.pnpubid AND p.pubname IN ( 'puba', 'pubb' ));
> pg_get_expr
> -------------
> (a > 10)
> (1 row)
>
>
> I think there should be no filter in this case, because "puba" publish table child
> without row filter. Thoughts?
>

I also think so.

> To fix this problem, we could use pg_get_publication_tables function in
> tablesync SQL to filter which publications the table belongs to. How about the
> following SQL, it would return NULL for "puba".
>
> SELECT DISTINCT pg_get_expr(pr.prqual, pr.prrelid)
> FROM pg_publication p
> LEFT OUTER JOIN pg_publication_rel pr
> ON (p.oid = pr.prpubid AND pr.prrelid = 16387),
> LATERAL pg_get_publication_tables(p.pubname) GPT
> WHERE GPT.relid = 16387 AND p.pubname IN ( 'puba', 'pubb' );
> pg_get_expr
> -------------
> (a > 10)
>
> (2 rows)
>

One advantage of this query is that it seems to have simplified the
original query by removing NOT conditions. I haven't tested this yet
but logically it appears correct to me.

--
With Regards,
Amit Kapila.