Re: Identify missing publications from publisher while create/alter subscription.

On Mon, Jan 25, 2021 at 10:32 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > I mean it doesn’t seem right to disallow to create the subscription if
> > the publisher doesn't exist, and my reasoning was even though the
> > publisher exists while creating the subscription you might drop it
> > later right?. So basically, now also we can create the same scenario
> > that a subscription may exist for the publication which does not
> > exist.
> >
>
> I would like to defer on documentation for this.
> I feel we should have the behavior similar to publication tables as given below, then it will be consistent and easier for the users:
>
> This is the behavior in case of table:
> Step 1:
> PUBLISHER SIDE:
> create table t1(c1 int);
> create table t2(c1 int);
> CREATE PUBLICATION mypub1 for table t1,t2;
> -- All above commands succeeds
> Step 2:
> SUBSCRIBER SIDE:
> -- Create subscription without creating tables will result in error:
> CREATE SUBSCRIPTION mysub1 CONNECTION 'dbname=source_rep host=localhost user=vignesh port=5432' PUBLICATION mypub1;
> ERROR: relation "public.t2" does not exist
> create table t1(c1 int);
> create table t2(c1 int);
>
> CREATE SUBSCRIPTION mysub1 CONNECTION 'dbname=source_rep host=localhost user=vignesh port=5432' PUBLICATION mypub1;
>
> postgres=# select * from pg_subscription;
> oid | subdbid | subname | subowner | subenabled | subbinary | substream | subconninfo | subslotname | subsynccommit | subpublications
> -------+---------+---------+----------+------------+-----------+-----------+---------------------------------------------------------+-------------+---------------+-----------------
> 16392 | 13756 | mysub1 | 10 | t | f | f | dbname=source_rep host=localhost user=vignesh port=5432 | mysub1 | off | {mypub1}
> (1 row)
>
> postgres=# select *,srrelid::oid::regclass from pg_subscription_rel;
> srsubid | srrelid | srsubstate | srsublsn | srrelid
> ---------+---------+------------+-----------+---------
> 16392 | 16389 | r | 0/1608BD0 | t2
> 16392 | 16384 | r | 0/1608BD0 | t1
>
> (2 rows)
> Step 3:
> PUBLISHER:
> drop table t2;
> create table t3;
> CREATE PUBLICATION mypub2 for table t1,t3;
>
> Step 4:
> SUBSCRIBER:
> postgres=# select *,srrelid::oid::regclass from pg_subscription_rel;
> srsubid | srrelid | srsubstate | srsublsn | srrelid
> ---------+---------+------------+-----------+---------
> 16392 | 16389 | r | 0/1608BD0 | t2
> 16392 | 16384 | r | 0/1608BD0 | t1
>
> (2 rows)
>
> postgres=# alter subscription mysub1 refresh publication ;
> ALTER SUBSCRIPTION
>
> -- Subscription relation will be updated.
> postgres=# select *,srrelid::oid::regclass from pg_subscription_rel;
> srsubid | srrelid | srsubstate | srsublsn | srrelid
> ---------+---------+------------+-----------+---------
> 16392 | 16384 | r | 0/1608BD0 | t1
> (1 row)
>
>
> -- Alter subscription fails while setting publication having a table that does not exist
> postgres=# alter subscription mysub1 set publication mysub2;
> ERROR: relation "public.t3" does not exist
>
> To maintain consistency, we should have similar behavior in case of publication too.
> If a publication which does not exist is specified during create subscription, then we should throw an error similar to step 2 behavior. Similarly if a publication which does not exist is specified during alter subscription, then we should throw an error similar to step 4 behavior. If publication is dropped after subscription is created, this should be removed when an alter subscription subname refresh publication is performed similar to step 4.
> Thoughts?

IIUC, your idea is to check if the publications (that are associated
with a subscription) are present in the publisher or not during ALTER
SUBSCRIPTION ... REFRESH PUBLICATION;. If that's the case, then I have

scenario:
1) subscription is created with pub1, pub2 and assume both the
publications are present in the publisher
2) pub1 and pub2 tables data is replicated properly
3) pub2 is dropped on the publisher
4) run alter subscription .. refresh publication on the subscriber, so
that the pub2 tables will be removed from the subscriber
5) for some reason, user creates pub2 again on the publisher and want
to replicated some tables
6) run alter subscription .. refresh publication on the subscriber, so
that the pub2 tables will be added to the subscriber table list

Now, if we remove the dropped publication pub2 in step 4 from the
subscription list(as per your above analysis and suggestion), then
after step 5, users will need to add the publication pub2 to the
subscription again. I feel this is a change in the current behaviour.
The existing behaviour on master doesn't mandate this as the dropped
publications are not removed from the subscription list at all.

To not mandate any new behaviour, I would suggest to have a new option
for ALTER SUBSCRIPTION ... REFRESH PUBLICATION WITH
(remove_dropped_publications = false). The new option
remove_dropped_publications will have a default value false, when set
to true it will check if the publications that are present in the
subscription list are actually existing on the publisher or not, if
not remove them from the list. And also in the documentation we need
to clearly mention the consequence of this new option setting to true,
that is, the dropped publications if created again will need to be
added to the subscription list again.

Thoughts?

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com