Обсуждение: [HACKERS] Getting error at the time of dropping subscription and few moreissues
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 intot 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 (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 (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. -- regards,tushar EnterpriseDB https://www.enterprisedb.com/ The Enterprise PostgreSQL Company https://sites.google.com/a/enterprisedb.com/old-new-touplestores/
Re: [HACKERS] Getting error at the time of dropping subscription andfew more issues
От
Masahiko Sawada
Дата:
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
Re: [HACKERS] Getting error at the time of dropping subscription andfew more issues
От
Peter Eisentraut
Дата:
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
Re: [HACKERS] Getting error at the time of dropping subscription andfew more issues
От
Masahiko Sawada
Дата:
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