Re: BUG #17132: About "ALTER SUBSCRIPTION ... ADD/DROP 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], 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] -
https://www.postgresql.org/message-id/OS0PR01MB5716935D4C2CC85A6143073F94EF9%40OS0PR01MB5716.jpnprd01.prod.outlook.com

--
With Regards,
Amit Kapila.