Re: row filtering for logical replication - Mailing list pgsql-hackers

On Mon, Mar 29, 2021 at 6:47 PM Euler Taveira <euler@eulerto.com> wrote:

>

Few comments:

==============

1. How can we specify row filters for multiple tables for a

publication? Consider a case as below:

postgres=# CREATE TABLE tab_rowfilter_1 (a int primary key, b text);

CREATE TABLE

postgres=# CREATE TABLE tab_rowfilter_2 (c int primary key);

CREATE TABLE

postgres=# CREATE PUBLICATION tap_pub_1 FOR TABLE tab_rowfilter_1,

tab_rowfilter_2 WHERE (a > 1000 AND b <> 'filtered');

ERROR:  column "a" does not exist

LINE 1: ...FOR TABLE tab_rowfilter_1, tab_rowfilter_2 WHERE (a > 1000 A...

                                                             ^

postgres=# CREATE PUBLICATION tap_pub_1 FOR TABLE tab_rowfilter_1,

tab_rowfilter_2  WHERE (c > 1000);

CREATE PUBLICATION

It gives an error when I tried to specify the columns corresponding to

the first relation but is fine for columns for the second relation.

Then, I tried few more combinations like below but that didn't work.

CREATE PUBLICATION tap_pub_1 FOR TABLE tab_rowfilter_1 As t1,

tab_rowfilter_2 As t2 WHERE (t1.a > 1000 AND t1.b <> 'filtered');

Will users be allowed to specify join conditions among columns from

multiple tables?

2.

+ /*

+ * Although ALTER PUBLICATION grammar allows WHERE clause to be specified

+ * for DROP TABLE action, it doesn't make sense to allow it. We implement

+ * this restriction here, instead of complicating the grammar to enforce

+ * it.

+ */

+ if (stmt->tableAction == DEFELEM_DROP)

+ {

+ ListCell   *lc;

+

+ foreach(lc, stmt->tables)

+ {

+ PublicationTable *t = lfirst(lc);

+

+ if (t->whereClause)

+ ereport(ERROR,

+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),

+ errmsg("cannot use a WHERE clause when removing table from

publication \"%s\"",

+ NameStr(pubform->pubname))));

+ }

+ }

Is there a reason to deal with this here separately rather than in the

ALTER PUBLICATION grammar?