BUG #17132: About "ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION"
The following bug has been logged on the website:
Bug reference: 17132
Logged by: Chen Jiaoqian
Email address: chenjq.jy@fujitsu.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?
On Thu, Aug 5, 2021 at 2:19 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
The following bug has been logged on the website:
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)
Can you check here what is the result of select * from pg_subscription_rel?
ALTER SUBSCRIPTION sub DROP PUBLICATION pub2 with (refresh = true);
select * from test2; -- select the data after dropping pub2
id
----
2
(1 row)
Can you check here what is the result of select * from pg_subscription_rel?
● 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)
Can you check here what is the result of select * from pg_subscription_rel?
I think ADD/DROP and SET clause are similar, shouldn't the data in the
table be consistent after re-adding the dropped publication?
Yeah, I think the result should be the same for both cases. The SET
seems to behave as per expectation. BTW, recently there is another bug
reported for ADD/DROP Publication [1]/messages/by-id/OS0PR01MB5716935D4C2CC85A6143073F94EF9@OS0PR01MB5716.jpnprd01.prod.outlook.com, you might want to check the
discussion and patch on that thread. It is quite possible that is a
different issue but there is no harm in looking at it.
[1]: /messages/by-id/OS0PR01MB5716935D4C2CC85A6143073F94EF9@OS0PR01MB5716.jpnprd01.prod.outlook.com
--
With Regards,
Amit Kapila.
On Thu, Aug 5, 2021 at 6:54 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Thu, Aug 5, 2021 at 2:19 PM PG Bug reporting form
<noreply@postgresql.org> wrote:I think ADD/DROP and SET clause are similar, shouldn't the data in the
table be consistent after re-adding the dropped publication?Yeah, I think the result should be the same for both cases. The SET
seems to behave as per expectation. BTW, recently there is another bug
reported for ADD/DROP Publication [1], you might want to check the
discussion and patch on that thread. It is quite possible that is a
different issue but there is no harm in looking at it.
I have checked and found that this is related to the bug we are
discussing on -hackers [1]/messages/by-id/OS0PR01MB5716935D4C2CC85A6143073F94EF9@OS0PR01MB5716.jpnprd01.prod.outlook.com as speculated by me. Now, what's going, in
this case, is that even after the drop publication, the corresponding
relation (test2) is not removed from the subscription list (verified
the same in pg_subscription_rel). So, next time, when you tried to add
the publication, it won't do anything special for the relation (test2)
as it is already part of the subscription. So, I suggest you keep an
eye on that thread [1]/messages/by-id/OS0PR01MB5716935D4C2CC85A6143073F94EF9@OS0PR01MB5716.jpnprd01.prod.outlook.com and see if the final patch resolves the problem
reported here as well.
[1]: /messages/by-id/OS0PR01MB5716935D4C2CC85A6143073F94EF9@OS0PR01MB5716.jpnprd01.prod.outlook.com
--
With Regards,
Amit Kapila.
[1] - /messages/by-id/OS0PR01MB5716935D4C2CC85A6143073F94EF9@OS0PR01MB5716.jpnprd01.prod.outlook.com
Sorry for the late replay and thank you for your answer. I will track this thread to confirm whether the problem is solved.
--
Best regards,
ChenJiaoqian
-----Original Message-----
From: Amit Kapila <amit.kapila16@gmail.com>
Sent: Friday, August 6, 2021 1:24 PM
To: Chen, Jiaoqian/陈 娇倩 <chenjq.jy@fujitsu.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17132: About "ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION"
On Thu, Aug 5, 2021 at 6:54 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Thu, Aug 5, 2021 at 2:19 PM PG Bug reporting form
<noreply@postgresql.org> wrote:I think ADD/DROP and SET clause are similar, shouldn't the data
in the table be consistent after re-adding the dropped publication?Yeah, I think the result should be the same for both cases. The SET
seems to behave as per expectation. BTW, recently there is another bug
reported for ADD/DROP Publication [1], you might want to check the
discussion and patch on that thread. It is quite possible that is a
different issue but there is no harm in looking at it.
I have checked and found that this is related to the bug we are discussing on -hackers [1]/messages/by-id/OS0PR01MB5716935D4C2CC85A6143073F94EF9@OS0PR01MB5716.jpnprd01.prod.outlook.com as speculated by me. Now, what's going, in this case, is that even after the drop publication, the corresponding relation (test2) is not removed from the subscription list (verified the same in pg_subscription_rel). So, next time, when you tried to add the publication, it won't do anything special for the relation (test2) as it is already part of the subscription. So, I suggest you keep an eye on that thread [1]/messages/by-id/OS0PR01MB5716935D4C2CC85A6143073F94EF9@OS0PR01MB5716.jpnprd01.prod.outlook.com and see if the final patch resolves the problem reported here as well.
[1]: /messages/by-id/OS0PR01MB5716935D4C2CC85A6143073F94EF9@OS0PR01MB5716.jpnprd01.prod.outlook.com
--
With Regards,
Amit Kapila.