RE: Added schema level support for publication. - Mailing list pgsql-hackers

From tanghy.fnst@fujitsu.com
Subject RE: Added schema level support for publication.
Date
Msg-id OS0PR01MB61138278359AE713EA24140DFB019@OS0PR01MB6113.jpnprd01.prod.outlook.com
Whole thread Raw
In response to RE: Added schema level support for publication.  ("tanghy.fnst@fujitsu.com" <tanghy.fnst@fujitsu.com>)
List pgsql-hackers
On Tuesday, June 29, 2021 11:25 AM tanghy.fnst@fujitsu.com <tanghy.fnst@fujitsu.com> wrote:
> 
> Thanks for your patch. The warnings are fixed.
> 
> But I found an issue while using your V8 patch, which is similar to [1]. The case
> is as below:
> Drop a schema from publication and refresh publication at subscriber, then
> insert into publisher table,  the inserts still replicated to subscriber. The expect
> result is that the data is no longer replicated.
> 

I also saw a problem while using "ALTER PUBLICATION ... ADD SCHEMA ...". The case is as below:
Add a schema to publication, then refresh publication at subscriber. Insert into publisher table,  the inserts couldn't
replicateto subscriber.
 

Steps to reproduce the case:
------publisher------
CREATE PUBLICATION testpub FOR SCHEMA public;

------subscriber------
CREATE SUBSCRIPTION testsub CONNECTION 'dbname=postgres port=5432' PUBLICATION testpub;

------publisher------
CREATE SCHEMA s1;
CREATE TABLE s1.t1 (a int PRIMARY KEY);
insert into s1.t1 values (1);
ALTER PUBLICATION testpub ADD SCHEMA s1;

------subscriber------
CREATE SCHEMA s1;
CREATE TABLE s1.t1 (a int PRIMARY KEY);
ALTER SUBSCRIPTION testsub REFRESH PUBLICATION;
postgres=# SELECT * FROM s1.t1;
 a
---
 1
(1 row)

------publisher------
insert into s1.t1 values (2);

------subscriber------
postgres=# SELECT * FROM s1.t1;
 a
---
 1
(1 row)

when I executed "ALTER PUBLICATION ... ADD TABLE ...",  rel_sync_cache_publication_cb callback function set
replicate_validto false, then it would validate the entry in get_rel_sync_entry function, and marked the pubactions to
true.so it worked ok.
 

In the case of  "ALTER PUBLICATION ... ADD SCHEMA ...", replicate_valid would not be set to false. Because of this, the
pubactionswere still false in get_rel_sync_entry function.
 
So I think the reason for it is similar to the one I reported before [1].

[1]
https://www.postgresql.org/message-id/OS0PR01MB61134B20314DE45795DD384CFB029%40OS0PR01MB6113.jpnprd01.prod.outlook.com

Regards
Tang

pgsql-hackers by date:

Previous
From: Peter Smith
Date:
Subject: Re: [HACKERS] logical decoding of two-phase transactions
Next
From: Fabien COELHO
Date:
Subject: Re: pgbench logging broken by time logic changes