Hi
po 24. 6. 2019 v 10:28 odesÃlatel Anthony Nowocien <anowoc...@gmail.com>
napsal:
> Hi,
> patch no longer applies (as of 12beta2).
>
> postgres@ubuntudev:~/pg_testing/source/postgresql-12beta2$ patch -p1 <
> drop-database-force-20190310_01.patch
> patching file doc/src/sgml/ref/drop_database.sgml
> patching file doc/src/sgml/ref/dropdb.sgml
> patching file src/backend/commands/dbcommands.c
> Hunk #1 succeeded at 489 (offset 2 lines).
> Hunk #2 succeeded at 779 (offset 2 lines).
> Hunk #3 succeeded at 787 (offset 2 lines).
> Hunk #4 succeeded at 871 (offset 2 lines).
> Hunk #5 succeeded at 1056 (offset 2 lines).
> Hunk #6 succeeded at 1186 (offset 2 lines).
> patching file src/backend/nodes/copyfuncs.c
> Hunk #1 succeeded at 3852 (offset 10 lines).
> patching file src/backend/nodes/equalfuncs.c
> Hunk #1 succeeded at 1666 (offset 3 lines).
> patching file src/backend/parser/gram.y
> Hunk #1 succeeded at 10194 (offset 45 lines).
> Hunk #2 succeeded at 10202 (offset 45 lines).
> patching file src/backend/storage/ipc/procarray.c
> Hunk #1 succeeded at 2906 (offset -14 lines).
> Hunk #2 succeeded at 2948 (offset -14 lines).
> patching file src/backend/tcop/utility.c
> patching file src/bin/scripts/dropdb.c
> Hunk #1 succeeded at 34 (offset 1 line).
> Hunk #2 succeeded at 50 (offset 1 line).
> Hunk #3 succeeded at 63 (offset 2 lines).
> Hunk #4 succeeded at 88 (offset 2 lines).
> Hunk #5 succeeded at 128 (offset 2 lines).
> Hunk #6 succeeded at 136 (offset 2 lines).
> Hunk #7 succeeded at 164 (offset 1 line).
> patching file src/bin/scripts/t/050_dropdb.pl
> patching file src/include/commands/dbcommands.h
> patching file src/include/nodes/parsenodes.h
> Hunk #1 succeeded at 3133 (offset 16 lines).
> patching file src/include/storage/procarray.h
> Hunk #1 FAILED at 112.
> 1 out of 1 hunk FAILED -- saving rejects to file
> src/include/storage/procarray.h.rej
>
> Could you please update it ? Thanks.
>
fixed
Regards
Pavel
Anthony
>
> The new status of this patch is: Waiting on Author
>
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 3fbdb33716..5d10ad1c92 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 15207bf75a..760b43de8c 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -489,7 +489,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",
@@ -779,7 +779,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;
@@ -787,6 +787,7 @@ dropdb(const char *dbname, bool missing_ok)
HeapTuple tup;
int notherbackends;
int npreparedxacts;
+ int loops = 0;
int nslots,
nslots_active;
int nsubscriptions;
@@ -870,12 +871,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.
@@ -1034,7 +1056,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",
@@ -1164,7 +1186,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 78deade89b..dc32a5506d 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3852,6 +3852,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 4f2ebe5118..8af53d8562 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1666,6 +1666,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 8311b1dd46..6ca2941821 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10194,6 +10194,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
@@ -10201,6 +10202,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 18a0f62ba6..fdcf0af12e 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -2906,7 +2906,8 @@ 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;
@@ -2948,6 +2949,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 9578b5c761..0b8a9f80bb 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 dacd8e5f1d..af86bb9f79 100644
--- a/src/bin/scripts/dropdb.c
+++ b/src/bin/scripts/dropdb.c
@@ -34,6 +34,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}
};
@@ -49,6 +50,7 @@ main(int argc, char *argv[])
enum trivalue prompt_password = TRI_DEFAULT;
bool echo = false;
bool interactive = false;
+ bool force = false;
PQExpBufferData sql;
@@ -61,7 +63,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)
{
@@ -86,6 +88,9 @@ main(int argc, char *argv[])
case 'i':
interactive = true;
break;
+ case 'f':
+ force = true;
+ break;
case 0:
/* this covers the long options */
break;
@@ -123,9 +128,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";
@@ -134,6 +136,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);
@@ -159,6 +164,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 94ded3c135..37a1257519 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3133,6 +3133,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 da8b672096..d0bd7db112 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -112,7 +112,7 @@ 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,
