Hi
út 18. 12. 2018 v 16:11 odesílatel Filip Rembiałkowski <
[email protected]> napsal:
> 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.
>
Still one my customer use a patch that implement FORCE on SQL level. It is
necessary under higher load when is not easy to synchronize clients.
Regards
Pavel
>
>
> Thanks,
> Filip
>
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index 68c43620ef..008cac25cc 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -496,7 +496,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
* to wait 5 sec. We try to raise warning after 1 minute and and raise
* a error after 5 minutes.
*/
- if (!CountOtherDBBackends(src_dboid, ¬herbackends, &npreparedxacts, true))
+ if (!CountOtherDBBackends(src_dboid, ¬herbackends, &npreparedxacts, true, false))
break;
if (loops++ % 12 == 0)
@@ -798,7 +798,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;
@@ -806,6 +806,7 @@ dropdb(const char *dbname, bool missing_ok)
HeapTuple tup;
int notherbackends;
int npreparedxacts;
+ int loops = 0;
int nslots,
nslots_active;
int nsubscriptions;
@@ -889,12 +890,33 @@ dropdb(const char *dbname, bool missing_ok)
*
* As in CREATE DATABASE, check this after other error conditions.
*/
- if (CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts, false))
- 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
+ * a error after 5 minutes.
+ */
+ if (!CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts, true, 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 exception 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.
@@ -1053,7 +1075,7 @@ RenameDatabase(const char *oldname, const char *newname)
*
* As in CREATE DATABASE, check this after other error conditions.
*/
- if (CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts, false))
+ if (CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts, false, false))
ereport(ERROR,
(errcode(ERRCODE_OBJECT_IN_USE),
errmsg("database \"%s\" is being accessed by other users",
@@ -1183,7 +1205,7 @@ movedb(const char *dbname, const char *tblspcname)
*
* As in CREATE DATABASE, check this after other error conditions.
*/
- if (CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts, false))
+ if (CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts, false, 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 8c8384cd6b..e147e594ab 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3755,6 +3755,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 68d38fcba1..ba7bee4bb0 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1655,6 +1655,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 7d0de99baf..358e878c70 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -9817,12 +9817,30 @@ DropdbStmt: DROP DATABASE database_name
n->dbname = $3;
n->missing_ok = FALSE;
$$ = (Node *)n;
+ n->force = false;
}
| DROP DATABASE IF_P EXISTS 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 8690a97dee..3080a81f7f 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -2895,10 +2895,13 @@ CountUserBackends(Oid roleid)
* continue (in simple implementation based only on PGPROC entries). In this case we should
* not calculate this process safely, becase createdb holds a lock.
*
+ * A option "force_terminate" enforce termination other sessions, that uses database. When we
+ * try to drop database, we should to calculate with all attached process.
*
*/
bool
-CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared, bool is_createdb_cmd)
+CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared,
+ bool is_createdb_cmd, bool force_terminate)
{
ProcArrayStruct *arrayP = procArray;
@@ -2943,6 +2946,18 @@ CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared, bool is_cre
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 82a707af7b..2304dce9d9 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -610,7 +610,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
/* no event triggers for global objects */
PreventTransactionChain(isTopLevel, "DROP DATABASE");
- dropdb(stmt->dbname, stmt->missing_ok);
+ dropdb(stmt->dbname, stmt->missing_ok, stmt->force);
}
break;
diff --git a/src/include/commands/dbcommands.h b/src/include/commands/dbcommands.h
index f42c8cdbe3..e267dc0a28 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 ecb6cd0249..d3147ab6f9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3053,6 +3053,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 298492afa1..f6561d11c7 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -113,7 +113,7 @@ extern void CancelDBBackends(Oid databaseid, ProcSignalReason sigmode, bool conf
extern int CountUserBackends(Oid roleid);
extern bool CountOtherDBBackends(Oid databaseId,
int *nbackends, int *nprepared,
- bool is_createdb_cmd);
+ bool is_createdb_cmd, bool force_terminate);
extern void XidCacheRemoveRunningXids(TransactionId xid,
int nxids, const TransactionId *xids,
