DROP SUBSCRIPTION, query cancellations and slot handling
Hi,
I have noticed the following behavior with DROP SUBSCRIPTION followed
by a cancel request. If the remote replication slot is dropped, the
subscription may still be present locally:
=# CREATE SUBSCRIPTION mysub CONNECTION 'port=5432 user=mpaquier
dbname=mpaquier' PUBLICATION mypub, insert_only;
NOTICE: 00000: created replication slot "mysub" on publisher
LOCATION: CreateSubscription, subscriptioncmds.c:408
NOTICE: 00000: synchronized table states
LOCATION: CreateSubscription, subscriptioncmds.c:434
CREATE SUBSCRIPTION
=# DROP SUBSCRIPTION mysub;
^CCancel request sent
NOTICE: 00000: dropped replication slot "mysub" on publisher
LOCATION: DropSubscription, subscriptioncmds.c:873
ERROR: 57014: canceling statement due to user request
LOCATION: ProcessInterrupts, postgres.c:2984
In this case the subscription is not dropped:
=# select subname from pg_subscription;
subname
---------
mysub
(1 row)
But trying to issue once again a drop results in an error:
=# DROP SUBSCRIPTION mysub;
ERROR: XX000: could not drop the replication slot "mysub" on publisher
DETAIL: The error was: ERROR: replication slot "mysub" does not exist
LOCATION: DropSubscription, subscriptioncmds.c:869
A subscription with the same name cannot be created either, so there
is nothing that the user can do except drop manually the slot on the
publisher. It seems to me that the moment where the slot is created
should be a point of no-return: the subcription has to be dropped on
the replication slot is dropped on the remote.
I am adding an open item.
Thanks,
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Apr 20, 2017 at 4:22 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
I am adding an open item.
Just adding something... When a subscription is created, if the step
synchronizing tables fails then CREATE SUBSCRIPTION fails but the slot
remains present on the publisher side, so trying to re-create the same
subscription results in an error:
=# CREATE SUBSCRIPTION mysub CONNECTION 'port=5432' PUBLICATION mypub,
insert_only;
NOTICE: 00000: Sleeping now...
NOTICE: 00000: created replication slot "mysub" on publisher
LOCATION: CreateSubscription, subscriptioncmds.c:411
ERROR: 42P01: relation "public.aa" does not exist
LOCATION: RangeVarGetRelidExtended, namespace.c:400
Time: 1033.739 ms (00:01.034)
=# CREATE SUBSCRIPTION mysub CONNECTION 'port=5432 user=mpaquier
dbname=mpaquier' PUBLICATION mypub, insert_only;
NOTICE: 00000: Sleeping now...
LOCATION: CreateSubscription, subscriptioncmds.c:376
ERROR: XX000: could not create replication slot "mysub": ERROR:
replication slot "mysub" already exists
LOCATION: libpqrcv_create_slot, libpqwalreceiver.c:776
I have created a simple table aa (a int) on the publisher first, where
a publication with ALL TABLES has been created:
CREATE PUBLICATION mypub FOR ALL TABLES;
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 20/04/17 09:22, Michael Paquier wrote:
Hi,
I have noticed the following behavior with DROP SUBSCRIPTION followed
by a cancel request. If the remote replication slot is dropped, the
subscription may still be present locally:
=# CREATE SUBSCRIPTION mysub CONNECTION 'port=5432 user=mpaquier
dbname=mpaquier' PUBLICATION mypub, insert_only;
NOTICE: 00000: created replication slot "mysub" on publisher
LOCATION: CreateSubscription, subscriptioncmds.c:408
NOTICE: 00000: synchronized table states
LOCATION: CreateSubscription, subscriptioncmds.c:434
CREATE SUBSCRIPTION
=# DROP SUBSCRIPTION mysub;
^CCancel request sent
NOTICE: 00000: dropped replication slot "mysub" on publisher
LOCATION: DropSubscription, subscriptioncmds.c:873
ERROR: 57014: canceling statement due to user request
LOCATION: ProcessInterrupts, postgres.c:2984In this case the subscription is not dropped:
=# select subname from pg_subscription;
subname
---------
mysub
(1 row)
But trying to issue once again a drop results in an error:
=# DROP SUBSCRIPTION mysub;
ERROR: XX000: could not drop the replication slot "mysub" on publisher
DETAIL: The error was: ERROR: replication slot "mysub" does not exist
LOCATION: DropSubscription, subscriptioncmds.c:869A subscription with the same name cannot be created either, so there
is nothing that the user can do except drop manually the slot on the
publisher. It seems to me that the moment where the slot is created
should be a point of no-return: the subcription has to be dropped on
the replication slot is dropped on the remote.
DROP SUBSCRIPTION mysub NODROP SLOT;
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, 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 20/04/17 09:35, Michael Paquier wrote:
On Thu, Apr 20, 2017 at 4:22 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:I am adding an open item.
Just adding something... When a subscription is created, if the step
synchronizing tables fails then CREATE SUBSCRIPTION fails but the slot
remains present on the publisher side, so trying to re-create the same
subscription results in an error:=# CREATE SUBSCRIPTION mysub CONNECTION 'port=5432' PUBLICATION mypub,
insert_only;
NOTICE: 00000: Sleeping now...
NOTICE: 00000: created replication slot "mysub" on publisher
LOCATION: CreateSubscription, subscriptioncmds.c:411
ERROR: 42P01: relation "public.aa" does not exist
LOCATION: RangeVarGetRelidExtended, namespace.c:400
Time: 1033.739 ms (00:01.034)
=# CREATE SUBSCRIPTION mysub CONNECTION 'port=5432 user=mpaquier
dbname=mpaquier' PUBLICATION mypub, insert_only;
NOTICE: 00000: Sleeping now...
LOCATION: CreateSubscription, subscriptioncmds.c:376
ERROR: XX000: could not create replication slot "mysub": ERROR:
replication slot "mysub" already exists
LOCATION: libpqrcv_create_slot, libpqwalreceiver.c:776
CREATE SUBSCRIPTION mysub CONNECTION 'port=5432' PUBLICATION mypub,
insert_only WITH(NOCREATE SLOT);
Or you can drop the slot manually on upstream.
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, 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 Thu, Apr 20, 2017 at 8:47 PM, Petr Jelinek
<petr.jelinek@2ndquadrant.com> wrote:
Or you can drop the slot manually on upstream.
Sure, but the point here is that if for example users have
client_min_messages set at least at warning, they may have no idea
that an underlying slot has been created. This is a confusing
experience for users.
As subscription is a self-contained concept, it seems to me that any
errors happening should at least try to do some cleanup action before
just giving up processing, that would be a less frustrating
experience.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 20/04/17 14:41, Michael Paquier wrote:
On Thu, Apr 20, 2017 at 8:47 PM, Petr Jelinek
<petr.jelinek@2ndquadrant.com> wrote:Or you can drop the slot manually on upstream.
Sure, but the point here is that if for example users have
client_min_messages set at least at warning, they may have no idea
that an underlying slot has been created. This is a confusing
experience for users.As subscription is a self-contained concept, it seems to me that any
errors happening should at least try to do some cleanup action before
just giving up processing, that would be a less frustrating
experience.
Hmm well since this only affects the synchronization of table
states/names, I guess we could just simply do that before we create the
slot as there is no expectancy of consistency between slot and the table
list snapshot.
Any other potential errors will be out of control of CreateSubscription
anyway.
Thoughts?
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, 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 4/20/17 08:41, Michael Paquier wrote:
As subscription is a self-contained concept, it seems to me that any
errors happening should at least try to do some cleanup action before
just giving up processing, that would be a less frustrating
experience.
This is the way it's designed.
The alternative is to do what we currently do for physical replication,
namely requiring the user to set up all the replication slots manually
beforehand. I don't think that's a better experience. There was a
thread about having pg_basebackup automatically create replication
slots. That will have to deal with the same issues.
--
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 4/20/17 10:19, Petr Jelinek wrote:
Hmm well since this only affects the synchronization of table
states/names, I guess we could just simply do that before we create the
slot as there is no expectancy of consistency between slot and the table
list snapshot.
I suppose that wouldn't hurt.
Prior to the table sync patch, a missing target relation would just show
up as an error later on in the logs. So having the error sooner
actually seems like a good change.
--
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 20/04/17 23:30, Peter Eisentraut wrote:
On 4/20/17 10:19, Petr Jelinek wrote:
Hmm well since this only affects the synchronization of table
states/names, I guess we could just simply do that before we create the
slot as there is no expectancy of consistency between slot and the table
list snapshot.I suppose that wouldn't hurt.
Prior to the table sync patch, a missing target relation would just show
up as an error later on in the logs. So having the error sooner
actually seems like a good change.
Very simple patch to make.
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Attachments:
Synchronize-table-list-before-creating-slot-in-CREAT.patchtext/plain; charset=UTF-8; name=Synchronize-table-list-before-creating-slot-in-CREAT.patchDownload
From c86411521694fd2729a81ebaa09c98e346886b59 Mon Sep 17 00:00:00 2001
From: Petr Jelinek <pjmodos@pjmodos.net>
Date: Fri, 21 Apr 2017 04:31:58 +0200
Subject: [PATCH] Synchronize table list before creating slot in CREATE
SUBSCRIPTION
This way the failure to synchronize the table list will not leave an
unused slot on publisher.
---
src/backend/commands/subscriptioncmds.c | 28 ++++++++++++++--------------
1 file changed, 14 insertions(+), 14 deletions(-)
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 35dccbc..c29a57e 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -395,20 +395,6 @@ CreateSubscription(CreateSubscriptionStmt *stmt, bool isTopLevel)
PG_TRY();
{
/*
- * If requested, create permanent slot for the subscription.
- * We won't use the initial snapshot for anything, so no need
- * to export it.
- */
- if (create_slot)
- {
- walrcv_create_slot(wrconn, slotname, false,
- CRS_NOEXPORT_SNAPSHOT, &lsn);
- ereport(NOTICE,
- (errmsg("created replication slot \"%s\" on publisher",
- slotname)));
- }
-
- /*
* Set sync state based on if we were asked to do data copy or
* not.
*/
@@ -432,6 +418,20 @@ CreateSubscription(CreateSubscriptionStmt *stmt, bool isTopLevel)
ereport(NOTICE,
(errmsg("synchronized table states")));
+
+ /*
+ * If requested, create permanent slot for the subscription.
+ * We won't use the initial snapshot for anything, so no need
+ * to export it.
+ */
+ if (create_slot)
+ {
+ walrcv_create_slot(wrconn, slotname, false,
+ CRS_NOEXPORT_SNAPSHOT, &lsn);
+ ereport(NOTICE,
+ (errmsg("created replication slot \"%s\" on publisher",
+ slotname)));
+ }
}
PG_CATCH();
{
--
2.7.4
On Fri, Apr 21, 2017 at 11:34 AM, Petr Jelinek
<petr.jelinek@2ndquadrant.com> wrote:
On 20/04/17 23:30, Peter Eisentraut wrote:
On 4/20/17 10:19, Petr Jelinek wrote:
Hmm well since this only affects the synchronization of table
states/names, I guess we could just simply do that before we create the
slot as there is no expectancy of consistency between slot and the table
list snapshot.I suppose that wouldn't hurt.
Prior to the table sync patch, a missing target relation would just show
up as an error later on in the logs. So having the error sooner
actually seems like a good change.Very simple patch to make.
+1 for that.
--
Michael--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 4/20/17 22:57, Michael Paquier wrote:
On Fri, Apr 21, 2017 at 11:34 AM, Petr Jelinek
<petr.jelinek@2ndquadrant.com> wrote:On 20/04/17 23:30, Peter Eisentraut wrote:
On 4/20/17 10:19, Petr Jelinek wrote:
Hmm well since this only affects the synchronization of table
states/names, I guess we could just simply do that before we create the
slot as there is no expectancy of consistency between slot and the table
list snapshot.I suppose that wouldn't hurt.
Prior to the table sync patch, a missing target relation would just show
up as an error later on in the logs. So having the error sooner
actually seems like a good change.Very simple patch to make.
+1 for that.
Committed that.
I don't think there is anything else open here.
--
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