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.

1 files changed, 40 insertions, 7 deletions

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, &notherbackends, &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, &notherbackends, &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, &notherbackends, &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,
@@ -1401,6 +1411,29 @@ check_db_file_conflict(Oid db_id)
 }
 
 /*
+ * 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
  *
  * Returns InvalidOid if database name not found.