From 4abd7b49f1e9eb1ccc934be5efabb6fd531d0141 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 4 Aug 2008 18:03:46 +0000 Subject: Improve CREATE/DROP/RENAME DATABASE so that when failing because the source or target database is being accessed by other users, it tells you whether the "other users" are live sessions or uncommitted prepared transactions. (Indeed, it tells you exactly how many of each, but that's mostly just because it was easy to do so.) This should help forestall the gotcha of not realizing that a prepared transaction is what's blocking the command. Per discussion. --- src/backend/commands/dbcommands.c | 47 +++++++++++++++++++++++++++++++++------ 1 file changed, 40 insertions(+), 7 deletions(-) (limited to 'src/backend/commands/dbcommands.c') diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c index 8b380157eef..f4bfa5cf6db 100644 --- a/src/backend/commands/dbcommands.c +++ b/src/backend/commands/dbcommands.c @@ -13,7 +13,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/dbcommands.c,v 1.209 2008/05/12 00:00:47 alvherre Exp $ + * $PostgreSQL: pgsql/src/backend/commands/dbcommands.c,v 1.210 2008/08/04 18:03:46 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -73,6 +73,7 @@ static bool get_db_info(const char *name, LOCKMODE lockmode, static bool have_createdb_privilege(void); static void remove_dbtablespaces(Oid db_id); static bool check_db_file_conflict(Oid db_id); +static int errdetail_busy_db(int notherbackends, int npreparedxacts); /* @@ -110,6 +111,8 @@ createdb(const CreatedbStmt *stmt) int encoding = -1; int dbconnlimit = -1; int ctype_encoding; + int notherbackends; + int npreparedxacts; createdb_failure_params fparms; /* Extract options from the statement node tree */ @@ -385,11 +388,12 @@ createdb(const CreatedbStmt *stmt) * potential waiting; we may as well throw an error first if we're gonna * throw one. */ - if (CheckOtherDBBackends(src_dboid)) + if (CountOtherDBBackends(src_dboid, ¬herbackends, &npreparedxacts)) ereport(ERROR, (errcode(ERRCODE_OBJECT_IN_USE), errmsg("source database \"%s\" is being accessed by other users", - dbtemplate))); + dbtemplate), + errdetail_busy_db(notherbackends, npreparedxacts))); /* * Select an OID for the new database, checking that it doesn't have a @@ -612,6 +616,8 @@ dropdb(const char *dbname, bool missing_ok) bool db_istemplate; Relation pgdbrel; HeapTuple tup; + int notherbackends; + int npreparedxacts; /* * Look up the target database's OID, and get exclusive lock on it. We @@ -671,11 +677,12 @@ dropdb(const char *dbname, bool missing_ok) * * As in CREATE DATABASE, check this after other error conditions. */ - if (CheckOtherDBBackends(db_id)) + if (CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts)) ereport(ERROR, (errcode(ERRCODE_OBJECT_IN_USE), errmsg("database \"%s\" is being accessed by other users", - dbname))); + dbname), + errdetail_busy_db(notherbackends, npreparedxacts))); /* * Remove the database's tuple from pg_database. @@ -764,6 +771,8 @@ RenameDatabase(const char *oldname, const char *newname) Oid db_id; HeapTuple newtup; Relation rel; + int notherbackends; + int npreparedxacts; /* * Look up the target database's OID, and get exclusive lock on it. We @@ -814,11 +823,12 @@ RenameDatabase(const char *oldname, const char *newname) * * As in CREATE DATABASE, check this after other error conditions. */ - if (CheckOtherDBBackends(db_id)) + if (CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts)) ereport(ERROR, (errcode(ERRCODE_OBJECT_IN_USE), errmsg("database \"%s\" is being accessed by other users", - oldname))); + oldname), + errdetail_busy_db(notherbackends, npreparedxacts))); /* rename */ newtup = SearchSysCacheCopy(DATABASEOID, @@ -1400,6 +1410,29 @@ check_db_file_conflict(Oid db_id) return result; } +/* + * Issue a suitable errdetail message for a busy database + */ +static int +errdetail_busy_db(int notherbackends, int npreparedxacts) +{ + /* + * We don't worry about singular versus plural here, since the English + * rules for that don't translate very well. But we can at least avoid + * the case of zero items. + */ + if (notherbackends > 0 && npreparedxacts > 0) + errdetail("There are %d other session(s) and %d prepared transaction(s) using the database.", + notherbackends, npreparedxacts); + else if (notherbackends > 0) + errdetail("There are %d other session(s) using the database.", + notherbackends); + else + errdetail("There are %d prepared transaction(s) using the database.", + npreparedxacts); + return 0; /* just to keep ereport macro happy */ +} + /* * get_database_oid - given a database name, look up the OID * -- cgit v1.2.3