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

On Thu, Aug 5, 2021 at 2:19 PM PG Bug reporting form
<noreply(at)postgresql(dot)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.