Getting error at the time of dropping subscription and few more issues

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]/messages/by-id/CAD21AoBYpyqTSw+=ES+xXtRGMPKh=pKiqjNxZKnNUae0pSt9bg@mail.gmail.com. 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]https://www.postgresql.org/docs/devel/static/logical-replication-subscription.html#logical-replication-subscription-slot, 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]: /messages/by-id/CAD21AoBYpyqTSw+=ES+xXtRGMPKh=pKiqjNxZKnNUae0pSt9bg@mail.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 5/12/17 13:25, Masahiko Sawada wrote:

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.

Why? These come directly has a result of the ALTER SUBSCRIPTION
command, so you see what they refer to.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, May 19, 2017 at 9:54 AM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:

On 5/12/17 13:25, Masahiko Sawada wrote:

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.

Why? These come directly has a result of the ALTER SUBSCRIPTION
command, so you see what they refer to.

Because I thought it would be helpful for DBA when the log is appeared
in server log. It doesn't appear by default though.

Regards,

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers