Thank you. Updated patch attached.
On Sat, Mar 9, 2019 at 2:53 AM Thomas Munro <thomas.mu...@gmail.com> wrote:
>
> On Wed, Mar 6, 2019 at 1:39 PM Filip Rembiałkowski
> <filip.rembialkow...@gmail.com> wrote:
> > Here is Pavel's patch rebased to master branch, added the dropdb
> > --force option, a test case & documentation.
>
> Hello,
>
> cfbot.cputube.org says this fails on Windows, due to a missing semicolon here:
>
> #ifdef HAVE_SETSID
> kill(-(proc->pid), SIGTERM);
> #else
> kill(proc->pid, SIGTERM)
> #endif
>
> The test case failed on Linux, I didn't check why exactly:
>
> Test Summary Report
> -------------------
> t/050_dropdb.pl (Wstat: 65280 Tests: 13 Failed: 2)
> Failed tests: 12-13
> Non-zero exit status: 255
> Parse errors: Bad plan. You planned 11 tests but ran 13.
>
> +/* Time to sleep after isuing SIGTERM to backends */
> +#define TERMINATE_SLEEP_TIME 1
>
> s/isuing/issuing/
>
> But, hmm, this macro doesn't actually seem to be used in the patch.
> Wait, is that because the retry loop forgot to actually include the
> sleep?
>
> + /* without "force" flag raise exception immediately, or after
> 5 minutes */
>
> Normally we call it an "error", not an "exception".
>
> --
> Thomas Munro
> https://enterprisedb.com
doc/src/sgml/ref/drop_database.sgml | 28 +++++++++++++++++++++----
doc/src/sgml/ref/dropdb.sgml | 14 +++++++++++++
src/backend/commands/dbcommands.c | 42 ++++++++++++++++++++++++++++---------
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/parser/gram.y | 18 ++++++++++++++++
src/backend/storage/ipc/procarray.c | 14 ++++++++++++-
src/backend/tcop/utility.c | 2 +-
src/bin/scripts/dropdb.c | 14 +++++++++----
src/bin/scripts/t/050_dropdb.pl | 6 ++++++
src/include/commands/dbcommands.h | 2 +-
src/include/nodes/parsenodes.h | 1 +
src/include/storage/procarray.h | 3 ++-
13 files changed, 124 insertions(+), 22 deletions(-)
diff --git a/doc/src/sgml/ref/drop_database.sgml b/doc/src/sgml/ref/drop_database.sgml
index 3ac06c984a..84df485e11 100644
--- a/doc/src/sgml/ref/drop_database.sgml
+++ b/doc/src/sgml/ref/drop_database.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-DROP DATABASE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
+DROP DATABASE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [ FORCE ]
</synopsis>
</refsynopsisdiv>
@@ -32,9 +32,11 @@ DROP DATABASE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
<command>DROP DATABASE</command> drops a database. It removes the
catalog entries for the database and deletes the directory
containing the data. It can only be executed by the database owner.
- Also, it cannot be executed while you or anyone else are connected
- to the target database. (Connect to <literal>postgres</literal> or any
- other database to issue this command.)
+ Also, it cannot be executed while you are connected to the target database.
+ (Connect to <literal>postgres</literal> or any other database to issue this
+ command.)
+ If anyone else is connected to the target database, this command will fail
+ - unless you use the <literal>FORCE</literal> option described below.
</para>
<para>
@@ -64,6 +66,24 @@ DROP DATABASE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>FORCE</literal></term>
+ <listitem>
+ <para>
+ Attempt to terminate all existing connections to the target database.
+ </para>
+ <para>
+ This will fail, if current user has no permissions to terminate other
+ connections. Required permissions are the same as with
+ <literal>pg_terminate_backend</literal>, described
+ in <xref linkend="functions-admin-signal"/>.
+
+ This will also fail, if the connections do not terminate in 60 seconds.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect1>
diff --git a/doc/src/sgml/ref/dropdb.sgml b/doc/src/sgml/ref/dropdb.sgml
index 38f38f01ce..365ba317c1 100644
--- a/doc/src/sgml/ref/dropdb.sgml
+++ b/doc/src/sgml/ref/dropdb.sgml
@@ -86,6 +86,20 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-f</option></term>
+ <term><option>--force</option></term>
+ <listitem>
+ <para>
+ Force termination of connected backends before removing the database.
+ </para>
+ <para>
+ This will add the <literal>FORCE</literal> option to the <literal>DROP
+ DATABASE</literal> command sent to the server.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-V</option></term>
<term><option>--version</option></term>
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index d207cd899f..0e7a23cb26 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -487,7 +487,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
* potential waiting; we may as well throw an error first if we're gonna
* throw one.
*/
- if (CountOtherDBBackends(src_dboid, ¬herbackends, &npreparedxacts))
+ if (CountOtherDBBackends(src_dboid, ¬herbackends, &npreparedxacts, false))
ereport(ERROR,
(errcode(ERRCODE_OBJECT_IN_USE),
errmsg("source database \"%s\" is being accessed by other users",
@@ -777,7 +777,7 @@ createdb_failure_callback(int code, Datum arg)
* DROP DATABASE
*/
void
-dropdb(const char *dbname, bool missing_ok)
+dropdb(const char *dbname, bool missing_ok, bool force)
{
Oid db_id;
bool db_istemplate;
@@ -785,6 +785,7 @@ dropdb(const char *dbname, bool missing_ok)
HeapTuple tup;
int notherbackends;
int npreparedxacts;
+ int loops = 0;
int nslots,
nslots_active;
int nsubscriptions;
@@ -868,12 +869,33 @@ dropdb(const char *dbname, bool missing_ok)
*
* As in CREATE DATABASE, check this after other error conditions.
*/
- if (CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts))
- ereport(ERROR,
- (errcode(ERRCODE_OBJECT_IN_USE),
- errmsg("database \"%s\" is being accessed by other users",
- dbname),
- errdetail_busy_db(notherbackends, npreparedxacts)));
+ for (;;)
+ {
+ /*
+ * CountOtherDBBackends check usage of database by other backends and try
+ * to wait 5 sec. We try to raise warning after 1 minute and and raise
+ * error after 5 minutes.
+ */
+ if (!CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts, force))
+ break;
+
+ if (force && loops++ % 12 == 0)
+ ereport(WARNING,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+ errmsg("source database \"%s\" is being accessed by other users",
+ dbname),
+ errdetail_busy_db(notherbackends, npreparedxacts)));
+
+ /* without "force" flag raise error immediately, or after 5 minutes */
+ if (!force || loops % 60 == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+ errmsg("source database \"%s\" is being accessed by other users",
+ dbname),
+ errdetail_busy_db(notherbackends, npreparedxacts)));
+
+ CHECK_FOR_INTERRUPTS();
+ }
/*
* Check if there are subscriptions defined in the target database.
@@ -1032,7 +1054,7 @@ RenameDatabase(const char *oldname, const char *newname)
*
* As in CREATE DATABASE, check this after other error conditions.
*/
- if (CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts))
+ if (CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts, false))
ereport(ERROR,
(errcode(ERRCODE_OBJECT_IN_USE),
errmsg("database \"%s\" is being accessed by other users",
@@ -1162,7 +1184,7 @@ movedb(const char *dbname, const char *tblspcname)
*
* As in CREATE DATABASE, check this after other error conditions.
*/
- if (CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts))
+ if (CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts, false))
ereport(ERROR,
(errcode(ERRCODE_OBJECT_IN_USE),
errmsg("database \"%s\" is being accessed by other users",
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index a8a735c247..f694a2890b 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3842,6 +3842,7 @@ _copyDropdbStmt(const DropdbStmt *from)
COPY_STRING_FIELD(dbname);
COPY_SCALAR_FIELD(missing_ok);
+ COPY_SCALAR_FIELD(force);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 3cab90e9f8..827fe0d6c1 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1663,6 +1663,7 @@ _equalDropdbStmt(const DropdbStmt *a, const DropdbStmt *b)
{
COMPARE_STRING_FIELD(dbname);
COMPARE_SCALAR_FIELD(missing_ok);
+ COMPARE_SCALAR_FIELD(force);
return true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e23e68fdb3..1a31b409c7 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10149,6 +10149,7 @@ DropdbStmt: DROP DATABASE database_name
DropdbStmt *n = makeNode(DropdbStmt);
n->dbname = $3;
n->missing_ok = false;
+ n->force = false;
$$ = (Node *)n;
}
| DROP DATABASE IF_P EXISTS database_name
@@ -10156,6 +10157,23 @@ DropdbStmt: DROP DATABASE database_name
DropdbStmt *n = makeNode(DropdbStmt);
n->dbname = $5;
n->missing_ok = true;
+ n->force = false;
+ $$ = (Node *)n;
+ }
+ | DROP DATABASE database_name FORCE
+ {
+ DropdbStmt *n = makeNode(DropdbStmt);
+ n->dbname = $3;
+ n->missing_ok = false;
+ n->force = true;
+ $$ = (Node *)n;
+ }
+ | DROP DATABASE IF_P EXISTS database_name FORCE
+ {
+ DropdbStmt *n = makeNode(DropdbStmt);
+ n->dbname = $5;
+ n->missing_ok = true;
+ n->force = true;
$$ = (Node *)n;
}
;
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index cf93357997..6e89d2d9df 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -2920,7 +2920,7 @@ CountUserBackends(Oid roleid)
* indefinitely.
*/
bool
-CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared)
+CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared, bool force_terminate)
{
ProcArrayStruct *arrayP = procArray;
@@ -2962,6 +2962,18 @@ CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared)
if ((pgxact->vacuumFlags & PROC_IS_AUTOVACUUM) &&
nautovacs < MAXAUTOVACPIDS)
autovac_pids[nautovacs++] = proc->pid;
+ else
+ {
+ if (force_terminate)
+ {
+ /* try to terminate backend */
+#ifdef HAVE_SETSID
+ kill(-(proc->pid), SIGTERM);
+#else
+ kill(proc->pid, SIGTERM);
+#endif
+ }
+ }
}
}
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 6ec795f1b4..ef9b66e573 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -601,7 +601,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
/* no event triggers for global objects */
PreventInTransactionBlock(isTopLevel, "DROP DATABASE");
- dropdb(stmt->dbname, stmt->missing_ok);
+ dropdb(stmt->dbname, stmt->missing_ok, stmt->force);
}
break;
diff --git a/src/bin/scripts/dropdb.c b/src/bin/scripts/dropdb.c
index df7823df99..caec73e89b 100644
--- a/src/bin/scripts/dropdb.c
+++ b/src/bin/scripts/dropdb.c
@@ -33,6 +33,7 @@ main(int argc, char *argv[])
{"interactive", no_argument, NULL, 'i'},
{"if-exists", no_argument, &if_exists, 1},
{"maintenance-db", required_argument, NULL, 2},
+ {"force", no_argument, NULL, 'f'},
{NULL, 0, NULL, 0}
};
@@ -48,6 +49,7 @@ main(int argc, char *argv[])
enum trivalue prompt_password = TRI_DEFAULT;
bool echo = false;
bool interactive = false;
+ bool force = false;
PQExpBufferData sql;
@@ -59,7 +61,7 @@ main(int argc, char *argv[])
handle_help_version_opts(argc, argv, "dropdb", help);
- while ((c = getopt_long(argc, argv, "h:p:U:wWei", long_options, &optindex)) != -1)
+ while ((c = getopt_long(argc, argv, "h:p:U:wWeif", long_options, &optindex)) != -1)
{
switch (c)
{
@@ -84,6 +86,9 @@ main(int argc, char *argv[])
case 'i':
interactive = true;
break;
+ case 'f':
+ force = true;
+ break;
case 0:
/* this covers the long options */
break;
@@ -121,9 +126,6 @@ main(int argc, char *argv[])
initPQExpBuffer(&sql);
- appendPQExpBuffer(&sql, "DROP DATABASE %s%s;",
- (if_exists ? "IF EXISTS " : ""), fmtId(dbname));
-
/* Avoid trying to drop postgres db while we are connected to it. */
if (maintenance_db == NULL && strcmp(dbname, "postgres") == 0)
maintenance_db = "template1";
@@ -132,6 +134,9 @@ main(int argc, char *argv[])
host, port, username, prompt_password,
progname, echo);
+ appendPQExpBuffer(&sql, "DROP DATABASE %s%s%s;",
+ (if_exists ? "IF EXISTS " : ""), fmtId(dbname), (force ? " FORCE" : ""));
+
if (echo)
printf("%s\n", sql.data);
result = PQexec(conn, sql.data);
@@ -158,6 +163,7 @@ help(const char *progname)
printf(_("\nOptions:\n"));
printf(_(" -e, --echo show the commands being sent to the server\n"));
printf(_(" -i, --interactive prompt before deleting anything\n"));
+ printf(_(" -f, --force force termination of connected backends\n"));
printf(_(" -V, --version output version information, then exit\n"));
printf(_(" --if-exists don't report error if database doesn't exist\n"));
printf(_(" -?, --help show this help, then exit\n"));
diff --git a/src/bin/scripts/t/050_dropdb.pl b/src/bin/scripts/t/050_dropdb.pl
index 25aa54a4ae..5f4ba20e2a 100644
--- a/src/bin/scripts/t/050_dropdb.pl
+++ b/src/bin/scripts/t/050_dropdb.pl
@@ -19,5 +19,11 @@ $node->issues_sql_like(
qr/statement: DROP DATABASE foobar1/,
'SQL DROP DATABASE run');
+$node->safe_psql('postgres', 'CREATE DATABASE foobar2');
+$node->issues_sql_like(
+ [ 'dropdb', '--force', 'foobar2' ],
+ qr/statement: DROP DATABASE foobar2 FORCE/,
+ 'SQL DROP DATABASE FORCE run');
+
$node->command_fails([ 'dropdb', 'nonexistent' ],
'fails with nonexistent database');
diff --git a/src/include/commands/dbcommands.h b/src/include/commands/dbcommands.h
index 28bf21153d..7ee4bbe13a 100644
--- a/src/include/commands/dbcommands.h
+++ b/src/include/commands/dbcommands.h
@@ -20,7 +20,7 @@
#include "nodes/parsenodes.h"
extern Oid createdb(ParseState *pstate, const CreatedbStmt *stmt);
-extern void dropdb(const char *dbname, bool missing_ok);
+extern void dropdb(const char *dbname, bool missing_ok, bool force);
extern ObjectAddress RenameDatabase(const char *oldname, const char *newname);
extern Oid AlterDatabase(ParseState *pstate, AlterDatabaseStmt *stmt, bool isTopLevel);
extern Oid AlterDatabaseSet(AlterDatabaseSetStmt *stmt);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index fe35783359..2df6d3013d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3117,6 +3117,7 @@ typedef struct DropdbStmt
NodeTag type;
char *dbname; /* database to drop */
bool missing_ok; /* skip error if db is missing? */
+ bool force; /* terminate all other sessions in db */
} DropdbStmt;
/* ----------------------
diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h
index bd24850989..3a75358eab 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -112,7 +112,8 @@ extern int CountDBConnections(Oid databaseid);
extern void CancelDBBackends(Oid databaseid, ProcSignalReason sigmode, bool conflictPending);
extern int CountUserBackends(Oid roleid);
extern bool CountOtherDBBackends(Oid databaseId,
- int *nbackends, int *nprepared);
+ int *nbackends, int *nprepared,
+ bool force_terminate);
extern void XidCacheRemoveRunningXids(TransactionId xid,
int nxids, const TransactionId *xids,
