BUG #17132: About "ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION" - Mailing list pgsql-bugs

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?