Hi,
I propose a simple patch (works-for-me), which adds --force (-f)
option to dropdb utility.
Pros: This seems to be a desired option for many sysadmins, as this
thread proves:
https://dba.stackexchange.com/questions/11893/force-drop-db-while-others-may-be-connected
Cons: another possible foot-gun for the unwary.
Obviously this patch needs some more work (see TODO note inside).
Please share opinions if this makes sense at all, and has any chance
going upstream.
The regression tests is simplistic, please help with an example of
multi-session test so I can follow.
Thanks,
Filip
diff --git a/doc/src/sgml/ref/drop_database.sgml b/doc/src/sgml/ref/drop_database.sgml
index 3ac06c984a..797763c28f 100644
--- a/doc/src/sgml/ref/drop_database.sgml
+++ b/doc/src/sgml/ref/drop_database.sgml
@@ -79,7 +79,8 @@ DROP DATABASE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
This command cannot be executed while connected to the target
database. Thus, it might be more convenient to use the program
<xref linkend="app-dropdb"/> instead,
- which is a wrapper around this command.
+ which is a wrapper around this command, and is also able
+ to terminate existing connections.
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/dropdb.sgml b/doc/src/sgml/ref/dropdb.sgml
index 38f38f01ce..29fb098e19 100644
--- a/doc/src/sgml/ref/dropdb.sgml
+++ b/doc/src/sgml/ref/dropdb.sgml
@@ -42,8 +42,9 @@ PostgreSQL documentation
<para>
<application>dropdb</application> is a wrapper around the
<acronym>SQL</acronym> command <xref linkend="sql-dropdatabase"/>.
- There is no effective difference between dropping databases via
- this utility and via other methods for accessing the server.
+ Other than the <option>--force</option> option, there is no effective
+ difference between dropping databases via this utility and via other
+ methods for accessing the server.
</para>
</refsect1>
@@ -86,6 +87,22 @@ 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 update the <literal>datallowconn</literal> attribute to
+ disallow incoming connections to target database, send
+ <literal>SIGTERM</literal> to target backends, and sleep some time
+ to allow the sessions to terminate.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-V</option></term>
<term><option>--version</option></term>
diff --git a/src/bin/scripts/dropdb.c b/src/bin/scripts/dropdb.c
index ba0038891d..4bfd8a9397 100644
--- a/src/bin/scripts/dropdb.c
+++ b/src/bin/scripts/dropdb.c
@@ -15,6 +15,10 @@
#include "fe_utils/string_utils.h"
+/* Time to sleep after isuing SIGTERM to backends */
+#define TERMINATE_SLEEP_TIME 1
+
+
static void help(const char *progname);
@@ -33,6 +37,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 +53,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 +65,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 +90,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 +130,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 +138,64 @@ main(int argc, char *argv[])
host, port, username, prompt_password,
progname, echo);
+ if (force)
+ {
+ /* TODO: revert this UPDATE in case removal fails for any reason */
+ appendPQExpBufferStr(&sql,
+ "UPDATE pg_catalog.pg_database\n"
+ " SET datallowconn = 'false'\n"
+ " WHERE datname OPERATOR(pg_catalog.=) ");
+ appendStringLiteralConn(&sql, dbname, conn);
+ appendPQExpBufferStr(&sql, ";\n");
+ if (echo)
+ printf("%s\n", sql.data);
+ result = PQexec(conn, sql.data);
+ if (PQresultStatus(result) != PGRES_COMMAND_OK)
+ {
+ fprintf(stderr, _("%s: database removal failed: %s"),
+ progname, PQerrorMessage(conn));
+ PQfinish(conn);
+ exit(1);
+ }
+
+ PQclear(result);
+
+ resetPQExpBuffer(&sql);
+
+ appendPQExpBufferStr(&sql,
+ "SELECT pg_catalog.pg_terminate_backend(pid)\n"
+ " FROM pg_catalog.pg_stat_activity\n"
+ " WHERE pid OPERATOR(pg_catalog.<>) pg_catalog.pg_backend_pid()\n"
+ " AND datname OPERATOR(pg_catalog.=) ");
+ appendStringLiteralConn(&sql, dbname, conn);
+ appendPQExpBufferStr(&sql, ";\n");
+
+ if (echo)
+ printf("%s\n", sql.data);
+ result = PQexec(conn, sql.data);
+ if (PQresultStatus(result) == PGRES_TUPLES_OK)
+ {
+ if (PQntuples(result) > 0)
+ printf("%s: sent SIGTERM to %d backend(s)\n",
+ progname, PQntuples(result));
+ pg_usleep(TERMINATE_SLEEP_TIME * 1000000);
+ }
+ else
+ {
+ fprintf(stderr, _("%s: database removal failed: %s"),
+ progname, PQerrorMessage(conn));
+ PQfinish(conn);
+ exit(1);
+ }
+
+ PQclear(result);
+
+ resetPQExpBuffer(&sql);
+ }
+
+ appendPQExpBuffer(&sql, "DROP DATABASE %s%s;",
+ (if_exists ? "IF EXISTS " : ""), fmtId(dbname));
+
if (echo)
printf("%s\n", sql.data);
result = PQexec(conn, sql.data);
@@ -158,6 +222,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..24663255ec 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: SELECT pg_catalog[.]pg_terminate_backend[(]pid[)]/,
+ 'SQL pg_terminate_backend run');
+
$node->command_fails([ 'dropdb', 'nonexistent' ],
'fails with nonexistent database');
