2015-10-16 21:12 GMT+02:00 Robert Haas <robertmh...@gmail.com>:
> On Fri, Oct 16, 2015 at 6:22 AM, Pavel Stehule <pavel.steh...@gmail.com>
> wrote:
> > in GoodData we have this feature implemented - little bit different
> named -
> > DROP DATABASE FORCE
> >
> > It is useful in complex environment with mix of pooled and not pooled
> > connections - and in our environment - about 2K databases per server with
> > lot of dropped / created databases per server / per day.
> >
> > I can publish this patch, if there will be any interest.
>
> I think this would be a useful feature. What would one do about
> prepared transactions?
>
It doesn't solve it - GoodData doesn't use it - so I didn't solve this
question - it emulates manual maintenance. In our solution can be some
opened issues.
Patch attached
Regards
Pavel
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
*** ./src/backend/commands/dbcommands.c.orig 2015-02-16 18:29:19.296790829 +0100
--- ./src/backend/commands/dbcommands.c 2015-02-17 10:59:50.999374567 +0100
***************
*** 456,462 ****
* 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))
break;
if (loops++ % 12 == 0)
--- 456,462 ----
* 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, false))
break;
if (loops++ % 12 == 0)
***************
*** 787,793 ****
* DROP DATABASE
*/
void
! dropdb(const char *dbname, bool missing_ok)
{
Oid db_id;
bool db_istemplate;
--- 787,793 ----
* DROP DATABASE
*/
void
! dropdb(const char *dbname, bool missing_ok, bool force)
{
Oid db_id;
bool db_istemplate;
***************
*** 795,800 ****
--- 795,801 ----
HeapTuple tup;
int notherbackends;
int npreparedxacts;
+ int loops = 0;
/*
* Look up the target database's OID, and get exclusive lock on it. We
***************
*** 864,875 ****
*
* 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)));
/*
* Remove the database's tuple from pg_database.
--- 865,897 ----
*
* As in CREATE DATABASE, check this after other error conditions.
*/
! 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();
! }
/*
* Remove the database's tuple from pg_database.
***************
*** 1007,1013 ****
*
* 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",
--- 1029,1035 ----
*
* As in CREATE DATABASE, check this after other error conditions.
*/
! if (CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts, false, false))
ereport(ERROR,
(errcode(ERRCODE_OBJECT_IN_USE),
errmsg("database \"%s\" is being accessed by other users",
***************
*** 1132,1138 ****
*
* 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",
--- 1154,1160 ----
*
* As in CREATE DATABASE, check this after other error conditions.
*/
! if (CountOtherDBBackends(db_id, ¬herbackends, &npreparedxacts, false, false))
ereport(ERROR,
(errcode(ERRCODE_OBJECT_IN_USE),
errmsg("database \"%s\" is being accessed by other users",
*** ./src/backend/nodes/copyfuncs.c.orig 2015-02-17 10:34:40.385775458 +0100
--- ./src/backend/nodes/copyfuncs.c 2015-02-17 10:35:31.041859691 +0100
***************
*** 3175,3180 ****
--- 3175,3181 ----
COPY_STRING_FIELD(dbname);
COPY_SCALAR_FIELD(missing_ok);
+ COPY_SCALAR_FIELD(force);
return newnode;
}
*** ./src/backend/nodes/equalfuncs.c.orig 2015-02-17 10:34:40.388775463 +0100
--- ./src/backend/nodes/equalfuncs.c 2015-02-17 10:35:31.043859695 +0100
***************
*** 1475,1480 ****
--- 1475,1481 ----
{
COMPARE_STRING_FIELD(dbname);
COMPARE_SCALAR_FIELD(missing_ok);
+ COMPARE_SCALAR_FIELD(force);
return true;
}
*** ./src/backend/parser/gram.y.orig 2015-02-17 10:35:21.334843550 +0100
--- ./src/backend/parser/gram.y 2015-02-17 10:35:31.049859705 +0100
***************
*** 7853,7870 ****
* This is implicitly CASCADE, no need for drop behavior
*****************************************************************************/
! DropdbStmt: DROP DATABASE database_name
{
DropdbStmt *n = makeNode(DropdbStmt);
n->dbname = $3;
n->missing_ok = FALSE;
$$ = (Node *)n;
}
! | DROP DATABASE IF_P EXISTS database_name
{
DropdbStmt *n = makeNode(DropdbStmt);
n->dbname = $5;
n->missing_ok = TRUE;
$$ = (Node *)n;
}
;
--- 7853,7872 ----
* This is implicitly CASCADE, no need for drop behavior
*****************************************************************************/
! DropdbStmt: DROP DATABASE database_name opt_force
{
DropdbStmt *n = makeNode(DropdbStmt);
n->dbname = $3;
n->missing_ok = FALSE;
+ n->force = $4;
$$ = (Node *)n;
}
! | DROP DATABASE IF_P EXISTS database_name opt_force
{
DropdbStmt *n = makeNode(DropdbStmt);
n->dbname = $5;
n->missing_ok = TRUE;
+ n->force = $6;
$$ = (Node *)n;
}
;
*** ./src/backend/storage/ipc/procarray.c.orig 2015-02-16 18:29:19.298790832 +0100
--- ./src/backend/storage/ipc/procarray.c 2015-02-17 10:44:43.422803881 +0100
***************
*** 2405,2414 ****
* continue (in simple implementation based only on PGPROC entries). In this case we should
* not calculate this process safely, becase createdb holds a lock.
*
*
*/
bool
! CountOtherDBBackends(Oid databaseId, int *nbackends, int *nprepared, bool is_createdb_cmd)
{
ProcArrayStruct *arrayP = procArray;
--- 2405,2417 ----
* 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, bool force_terminate)
{
ProcArrayStruct *arrayP = procArray;
***************
*** 2453,2458 ****
--- 2456,2473 ----
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
+ }
+ }
}
}
*** ./src/backend/tcop/utility.c.orig 2015-02-17 10:35:21.341843562 +0100
--- ./src/backend/tcop/utility.c 2015-02-17 10:35:31.053859711 +0100
***************
*** 940,946 ****
DropdbStmt *stmt = (DropdbStmt *) parsetree;
PreventTransactionChain(isTopLevel, "DROP DATABASE");
! dropdb(stmt->dbname, stmt->missing_ok);
}
break;
--- 940,946 ----
DropdbStmt *stmt = (DropdbStmt *) parsetree;
PreventTransactionChain(isTopLevel, "DROP DATABASE");
! dropdb(stmt->dbname, stmt->missing_ok, stmt->force);
}
break;
*** ./src/include/commands/dbcommands.h.orig 2015-02-17 10:35:21.344843567 +0100
--- ./src/include/commands/dbcommands.h 2015-02-17 10:35:31.054859713 +0100
***************
*** 53,59 ****
} xl_dbase_drop_rec;
extern void createdb(const CreatedbStmt *stmt);
! extern void dropdb(const char *dbname, bool missing_ok);
extern void RenameDatabase(const char *oldname, const char *newname);
extern void AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel);
extern void AlterDatabaseSet(AlterDatabaseSetStmt *stmt);
--- 53,59 ----
} xl_dbase_drop_rec;
extern void createdb(const CreatedbStmt *stmt);
! extern void dropdb(const char *dbname, bool missing_ok, bool force);
extern void RenameDatabase(const char *oldname, const char *newname);
extern void AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel);
extern void AlterDatabaseSet(AlterDatabaseSetStmt *stmt);
*** ./src/include/nodes/parsenodes.h.orig 2015-02-17 10:35:21.347843571 +0100
--- ./src/include/nodes/parsenodes.h 2015-02-17 10:35:31.055859715 +0100
***************
*** 2350,2355 ****
--- 2350,2356 ----
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;
/* ----------------------
*** ./src/include/storage/procarray.h.orig 2015-02-17 10:35:31.056859716 +0100
--- ./src/include/storage/procarray.h 2015-02-17 10:57:20.417113491 +0100
***************
*** 72,78 ****
extern int CountUserBackends(Oid roleid);
extern bool CountOtherDBBackends(Oid databaseId,
int *nbackends, int *nprepared,
! bool is_createdb_cmd);
extern void XidCacheRemoveRunningXids(TransactionId xid,
int nxids, const TransactionId *xids,
--- 72,78 ----
extern int CountUserBackends(Oid roleid);
extern bool CountOtherDBBackends(Oid databaseId,
int *nbackends, int *nprepared,
! bool is_createdb_cmd, bool force_terminate);
extern void XidCacheRemoveRunningXids(TransactionId xid,
int nxids, const TransactionId *xids,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
