BUG #17132: About "ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION"

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: chenjq(dot)jy(at)fujitsu(dot)com
Subject: BUG #17132: About "ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION"
Date: 2021-08-05 02:05:55
Message-ID: 17132-6a702189086c6243@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17132
Logged by: Chen Jiaoqian
Email address: chenjq(dot)jy(at)fujitsu(dot)com
PostgreSQL version: 14beta2
Operating system: Red Hat Enterprise Linux Server release 7.8
Description:

Hi, Author

When I use "ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION" to add/drop
publication, the count of data is strange after re-adding dropped
publication.
As far as I know, there are two ways to add/drop subscribed publications
in PG14 beta2:
1) ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION (PG14 new feature)
2) ALTER SUBSCRIPTION ... SET PUBLICATION (existing feature)

Use the above two ways to perform the following operations:
Step 1. Create two instances, one as the publisher and one as the
subscriber.
Step 2. On the publisher, create two tables test1 and test2 and
insert a piece of data into test2,
and create publication pub1 for test1, publication pub2 for
test2.
Step 3. On the subscriber, create two tables test1 and test2 that
are the same as those on the publisher,
and create a subscription sub to subscribe to pub1. Then add
pub2 into the sub.
Step 4. Drop pub2 from the list of publications on sub.
Step 5. On the publisher, insert a new piece of data into test2.
Step 6. On the subscriber, re-add pub2 into the sub.

At this time, select the data from test2 on the subscriber.
Use way 1), table test2 has only one piece of data from Step 2.
Use way 2), table test2 has three pieces of data, one from Step 2 and
two from Step 5.
The details of the steps for the above two ways are as follow:

■ Use way 1)
● On the publisher
create table test1(id int);
create table test2(id int);
insert into test2 values(2);
create publication pub1 for table test1;
create publication pub2 for table test2;

● On the subscriber
create table test1(id int);
create table test2(id int);
create subscription sub connection 'dbname=postgres, ...' publication
pub1;
ALTER SUBSCRIPTION sub ADD PUBLICATION pub2 with (refresh = true);
select * from test2; -- select the data after adding publication
pub2
id
----
2
(1 row)

ALTER SUBSCRIPTION sub DROP PUBLICATION pub2 with (refresh = true);
select * from test2; -- select the data after dropping pub2
id
----
2
(1 row)

● On the publisher
insert into test2 values(3);

● On the subscriber
ALTER SUBSCRIPTION sub ADD PUBLICATION pub2 with (refresh = true);
select * from test2; -- select the data after re-adding dropped pub2
id
----
2
(1 row)

■ Use way 2)
● On the publisher
create table test1(id int);
create table test2(id int);
insert into test2 values(2);
create publication pub1 for table test1;
create publication pub2 for table test2;

● On the subscriber
create table test1(id int);
create table test2(id int);
create subscription sub connection 'dbname=postgres, ...' publication
pub1;
ALTER SUBSCRIPTION sub SET PUBLICATION pub1,pub2 with (refresh =
true);
select * from test2; -- select the data after adding publication
pub2
id
----
2
(1 row)
ALTER SUBSCRIPTION sub SET PUBLICATION pub1 with (refresh = true);
select * from test2; -- select the data after dropping pub2
id
----
2
(1 row)

● On the publisher
insert into test2 values(3);

● On the subscriber
ALTER SUBSCRIPTION sub SET PUBLICATION pub1,pub2 with (refresh =
true);
select * from test2; -- select the data after re-adding dropped pub2
id
----
2
2
3
(3 rows)

I think ADD/DROP and SET clause are similar, shouldn't the data in the
table be consistent after re-adding the dropped publication?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Mike Knowsley 2021-08-05 02:45:19 Can not cancel a call to a function that has opened a refcursor
Previous Message Tom Lane 2021-08-05 01:35:22 Re: CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3