Thread: [HACKERS] Better error message for trying to drop a DB with open subscriptions?

[HACKERS] Better error message for trying to drop a DB with open subscriptions?

From

Josh Berkus

Date:

21 July 2017, 05:09:14

All:

The problem:

postgres=# drop database bookdata;
ERROR:  database "bookdata" is being accessed by other users
DETAIL:  There is 1 other session using the database.
postgres=# \c bookdata
You are now connected to database "bookdata" as user "postgres".
bookdata=# drop subscription wholedb;
NOTICE:  dropped replication slot "wholedb" on publisher
DROP SUBSCRIPTION
bookdata=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop database bookdata;
DROP DATABASE

Is there any easy way for us to detect that the "user" accessing the
target database is actually a logical replication subscription, and give
the DBA a better error message (e.g. "database 'bookdata' still has open
subscrptions")?

-- 
Josh Berkus
Containers & Databases Oh My!

Re: [HACKERS] Better error message for trying to drop a DB with open subscriptions?

From

Craig Ringer

Date:

21 July 2017, 07:57:13

On 21 July 2017 at 07:09, Josh Berkus <josh@berkus.org> wrote:

All:

The problem:

postgres=# drop database bookdata;
ERROR: database "bookdata" is being accessed by other users
DETAIL: There is 1 other session using the database.
postgres=# \c bookdata
You are now connected to database "bookdata" as user "postgres".
bookdata=# drop subscription wholedb;
NOTICE: dropped replication slot "wholedb" on publisher
DROP SUBSCRIPTION
bookdata=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop database bookdata;
DROP DATABASE

Is there any easy way for us to detect that the "user" accessing the
target database is actually a logical replication subscription, and give
the DBA a better error message (e.g. "database 'bookdata' still has open
subscrptions")?

Good idea. Also, this affects any active logical (db-specific) replication slot, not just built-in logical replication.

CountOtherDBBackends reports prepared xacts separately already, and errdetail_busy_db uses that to report the two separately. Since we have slot attachment data I expect reporting attached replication slots would not be hard either; you might be able to prep a patch for that in a few hours.

Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Better error message for trying to drop a DB with open subscriptions?

From

Jeff Janes

Date:

21 July 2017, 11:04:17

On Thu, Jul 20, 2017 at 4:09 PM, Josh Berkus <josh@berkus.org> wrote:

All:

The problem:

postgres=# drop database bookdata;
ERROR: database "bookdata" is being accessed by other users
DETAIL: There is 1 other session using the database.
postgres=# \c bookdata
You are now connected to database "bookdata" as user "postgres".
bookdata=# drop subscription wholedb;
NOTICE: dropped replication slot "wholedb" on publisher
DROP SUBSCRIPTION
bookdata=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop database bookdata;
DROP DATABASE

Is there any easy way for us to detect that the "user" accessing the
target database is actually a logical replication subscription, and give
the DBA a better error message (e.g. "database 'bookdata' still has open
subscrptions")?

Better yet would be to just cascade the drop, but I assume that would be harder to do.

Cheers,

Jeff