Re: row filtering for logical replication

On 2018/11/01 0:29, Euler Taveira wrote:
> Em qua, 28 de fev de 2018 às 20:03, Euler Taveira
> <euler(at)timbira(dot)com(dot)br> escreveu:
>> The attached patches add support for filtering rows in the publisher.
>>
> I rebased the patch. I added row filtering for initial
> synchronization, pg_dump support and psql support. 0001 removes unused
> code. 0002 reduces memory use. 0003 passes only structure member that
> is used in create_estate_for_relation. 0004 reuses a parser node for
> row filtering. 0005 is the feature. 0006 prints WHERE expression in
> psql. 0007 adds pg_dump support. 0008 is only for debug purposes (I'm
> not sure some of these messages will be part of the final patch).
> 0001, 0002, 0003 and 0008 are not mandatory for this feature.
>
> Comments?
>
>

Hi,

I reviewed your patches and I found a bug when I tested ALTER
PUBLICATION statement.

In short, ALTER PUBLICATION SET with a WHERE clause does not applied new
WHERE clause.

I describe the outline of the test I did and my conclusion.

[TEST]
I show the test case I tried in below.

(1)Publisher and Subscriber

I executed each statement on the publisher and the subscriber.

```
testdb=# CREATE PUBLICATION pub_testdb_t FOR TABLE t WHERE (id > 10);
CREATE PUBLICATION
```

```
testdb=# CREATE SUBSCRIPTION sub_testdb_t CONNECTION 'dbname=testdb
port=5432 user=postgres' PUBLICATION pub_testdb_t;
NOTICE: created replication slot "sub_testdb_t" on publisher
CREATE SUBSCRIPTION
```

(2)Publisher

I executed these statements shown below.

testdb=# INSERT INTO t VALUES (1,1);
INSERT 0 1
testdb=# INSERT INTO t VALUES (11,11);
INSERT 0 1

(3)Subscriber

I confirmed that the CREATE PUBLICATION statement worked well.

```
testdb=# SELECT * FROM t;
id | data
----+------
11 | 11
(1 row)
```

(4)Publisher
After that, I executed ALTER PUBLICATION with a WHERE clause and
inserted a new row.

```
testdb=# ALTER PUBLICATION pub_testdb_t SET TABLE t WHERE (id > 5);
ALTER PUBLICATION

testdb=# INSERT INTO t VALUES (7,7);
INSERT 0 1

testdb=# SELECT * FROM t;
id | data
----+------
1 | 1
11 | 11
7 | 7
(3 rows)
```

(5)Subscriber
I confirmed that the change of WHERE clause set by ALTER PUBLICATION
statement was ignored.

```
testdb=# SELECT * FROM t;
id | data
----+------
11 | 11
(1 row)
```

[Conclusion]
I think AlterPublicationTables()@publicationcmds.c has a bug.

In the foreach(oldlc, oldrelids) loop, oldrel must be appended to
delrels if oldrel or newrel has a WHERE clause. However, the current
implementation does not, therefore, old WHERE clause is not deleted and
the new WHERE clause is ignored.

This is my speculation. It may not be correct, but , at least, it is a
fact that ALTER PUBLICATION with a WHERE clause is not functioned in my
environment and my operation described in above.

Best regards,