Re: [HACKERS] Getting error at the time of dropping subscription andfew more issues - Mailing list pgsql-hackers

On Fri, May 12, 2017 at 9:35 PM, tushar <tushar.ahuja@enterprisedb.com> wrote:
> Hi,
>
> There are few more issues , found in logical replication
>
> (1)ERROR:  tuple concurrently updated
>
> Publication Server - (X machine)
> \\create table \ create publication \ insert rows
>  create table t(n int);
>  create publication pub for table t;
>  insert into t values (generate_series(1,1000000));
>
> Subscription Server-(Y machine)
> \\create table t / create subscription
> create table t(n int);
> create subscription sub connection 'dbname=postgres  port=5000 user=centos
> password=a' publication pub;
>
> \\drop subscription and  re-create  (repeat this 2-3 times)
> postgres=# drop subscription sub;
> NOTICE:  dropped replication slot "sub" on publisher
> DROP SUBSCRIPTION
> postgres=# create subscription sub connection 'dbname=postgres port=5000
> user=centos  password=a' publication pub;
> NOTICE:  synchronized table states
> NOTICE:  created replication slot "sub" on publisher
> CREATE SUBSCRIPTION
> postgres=# select count(*) from t;
>   count
> ---------
>  1000000
> (1 row)
>
> postgres=# drop subscription sub;
> ERROR:  tuple concurrently updated

I wonder the subscriber had already behaved oddly during dropping and
creating the subscription repeatedly. An issue related to doing DROP
SUBSCRIPTION repeatedly is reported on another thread and is under the
discussion[1]. This issue might be relevant with that.

>
> (2) Not able to drop the subscription even 'nocreate slot' is specified
>
> postgres=# create subscription s2s1 connection 'dbname=postgres port=5000
> user=t  password=a' publication t with (nocreate
> slot,enabled,copydata,SYNCHRONOUS_COMMIT='on');
> NOTICE:  synchronized table states
> CREATE SUBSCRIPTION
>
> --not able to drop subscription ,  i have checked on Publication - no such
> slot created but still it is looking for slot.
> postgres=# drop subscription s2s1;
> ERROR:  could not drop the replication slot "s2s1" on publisher
> DETAIL:  The error was: ERROR:  replication slot "s2s1" does not exist

As documentation mentions[2], we can drop subscription after disabled
and set slot name to NONE by ALTER SUBSCRIPTION.

> (3)Alter publication SET  command doesn't give you NOTICE message about
> tables which got removed.
>
> postgres=# create publication pub for table t,t1,t2 ;
> CREATE PUBLICATION
>
> postgres=# select * from pg_publication_tables ;
>  pubname | schemaname | tablename
> ---------+------------+-----------
>  pub     | public     | t
>  pub     | public     | t1
>  pub     | public     | t2
> (3 rows)
>
> postgres=# alter publication pub set table t;
> ALTER PUBLICATION
>
> postgres=# select * from pg_publication_tables ;
>  pubname | schemaname | tablename
> ---------+------------+-----------
>  pub     | public     | t
> (1 row)
>
> in subscription -  (we are getting NOTICE message, about tables which got
> removed)
>
> postgres=#  alter subscription sub set publication pub refresh;
> NOTICE:  removed subscription for table public.t1
> NOTICE:  removed subscription for table public.t2
> ALTER SUBSCRIPTION
>
> I think  - in publication too ,we should provide NOTICE messages.
>

I guess one of the reason why we emit such a NOTICE message is that
subscriber cannot control which table the upstream server replicate.
So if a table got disassociated on the publisher the subscriber should
report that to user. On the other hand, since the publication can
control it and the changes are obvious, I'm not sure we really need to
do that.

BTW I think it's better for the above NOTICE message to have subscription name.

[1] https://www.postgresql.org/message-id/CAD21AoBYpyqTSw%2B%3DES%2BxXtRGMPKh%3DpKiqjNxZKnNUae0pSt9bg%40mail.gmail.com
[2]
https://www.postgresql.org/docs/devel/static/logical-replication-subscription.html#logical-replication-subscription-slot


Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center