>If the user owns objects, that will prevent this from working also. I
>have the feeling that adding DROP OWNED BY and/or REASSIGNED OWNED BY
>calls to this utility would be a bit excessive, but who knows.
Please find attached the first attempt to drop drop the client connections.
I have added an option -k, --kill instead of force since killing client
connection does not guarantee -drop force-.
Regards
On Tuesday, January 14, 2014 8:06 PM, Alvaro Herrera <alvhe...@2ndquadrant.com>
wrote:
salah jubeh wrote:
> For the sake of completeness:
> 1. I think also, I need also to temporary disallow conecting to the database,
> is that right?
> 2. Is there other factors can hinder dropping database?
If the user owns objects, that will prevent this from working also. I
have the feeling that adding DROP OWNED BY and/or REASSIGNED OWNED BY
calls to this utility would be a bit excessive, but who knows.
> 3. Should I write two patches one for pg_version>=9.2 and one for
> pg_version<9.2
No point -- nothing gets applied to branches older than current
development anyway.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--- dropdb_orig.c 2014-01-16 15:21:51.059243617 +0100
+++ dropdb.c 2014-01-16 16:38:57.419414200 +0100
@@ -32,6 +32,7 @@
{"echo", no_argument, NULL, 'e'},
{"interactive", no_argument, NULL, 'i'},
{"if-exists", no_argument, &if_exists, 1},
+ {"kill", no_argument, NULL, 'k'},
{"maintenance-db", required_argument, NULL, 2},
{NULL, 0, NULL, 0}
};
@@ -48,6 +49,8 @@
enum trivalue prompt_password = TRI_DEFAULT;
bool echo = false;
bool interactive = false;
+ bool kill = false;
+ char *database_conn_limit = "-1";
PQExpBufferData sql;
@@ -59,7 +62,7 @@
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:wWeik", long_options, &optindex)) != -1)
{
switch (c)
{
@@ -84,6 +87,9 @@
case 'i':
interactive = true;
break;
+ case 'k':
+ kill = true;
+ break;
case 0:
/* this covers the long options */
break;
@@ -121,8 +127,6 @@
initPQExpBuffer(&sql);
- appendPQExpBuffer(&sql, "DROP DATABASE %s%s;\n",
- (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)
@@ -131,11 +135,64 @@
conn = connectMaintenanceDatabase(maintenance_db,
host, port, username, prompt_password, progname);
+ /*disallow database connections and terminate client connections*/
+ if (kill)
+ {
+ appendPQExpBuffer(&sql, "SELECT datconnlimit FROM pg_database WHERE datname= '%s';",fmtId(dbname));
+ result = executeQuery(conn, sql.data, progname, echo);
+ // get the datconnĺimit to do cleanup in case of dropdb fail
+ if (PQntuples(result) == 1)
+ {
+ database_conn_limit = PQgetvalue(result, 0, 0);
+ } else
+ {
+ fprintf(stderr, _("%s: database removal failed: %s\n"),
+ progname, dbname);
+ PQclear(result);
+ PQfinish(conn);
+ exit(1);
+ }
+ PQclear(result);
+ resetPQExpBuffer(&sql);
+ //new connections are not allowed
+ appendPQExpBuffer(&sql, "UPDATE pg_database SET datconnlimit = 0 WHERE datname= '%s';\n",fmtId(dbname));
+ if (echo)
+ printf("%s", sql.data);
+ result = PQexec(conn, sql.data);
+ if (PQresultStatus(result) != PGRES_COMMAND_OK)
+ {
+ fprintf(stderr, _("%s: database removal failed: %s\n"),
+ progname, dbname);
+ PQclear(result);
+ PQfinish(conn);
+ exit(1);
+ }
+ PQclear(result);
+ resetPQExpBuffer(&sql);
+ // terminate client connections
+ appendPQExpBuffer(&sql, "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname ='%s';",fmtId(dbname));
+ result = executeQuery(conn, sql.data, progname, echo);
+ PQclear(result);
+ resetPQExpBuffer(&sql);
+ }
+
+ appendPQExpBuffer(&sql, "DROP DATABASE %s%s;\n",
+ (if_exists ? "IF EXISTS " : ""), fmtId(dbname));
+
if (echo)
printf("%s", sql.data);
result = PQexec(conn, sql.data);
if (PQresultStatus(result) != PGRES_COMMAND_OK)
{
+ //cleanup: reset datconnlimit
+ if (kill)
+ {
+ resetPQExpBuffer(&sql);
+ appendPQExpBuffer(&sql, "UPDATE pg_database SET datconnlimit = %s WHERE datname= '%s';",database_conn_limit,fmtId(dbname));
+ if (echo)
+ printf("%s", sql.data);
+ result = PQexec(conn, sql.data);
+ }
fprintf(stderr, _("%s: database removal failed: %s"),
progname, PQerrorMessage(conn));
PQfinish(conn);
@@ -159,6 +216,7 @@
printf(_(" -i, --interactive prompt before deleting anything\n"));
printf(_(" -V, --version output version information, then exit\n"));
printf(_(" --if-exists don't report error if database doesn't exist\n"));
+ printf(_(" -k, --kill kill client connections\n"));
printf(_(" -?, --help show this help, then exit\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
