>$ createdb -U postgres hoge
>$ psql -d hoge -U postgres
>hoge=# create table test (col text);
>hoge=# insert into test select repeat(chr(code),10000) from
>generate_series(1,100000) code;
><Execute dropdb -k while the client is inserting many tuples into database>
>$ dropdb -k hoge
>2014-01-29 23:10:49 JST FATAL: terminating connection due to
>administrator command
>2014-01-29 23:10:49 JST STATEMENT: insert into test select
>repeat(chr(code),10000) from generate_series(1,2000000) code;
>2014-01-29 23:10:54 JST ERROR: database "hoge" is being accessed by other
>users
>2014-01-29 23:10:54 JST DETAIL: There is 1 other session using the database.
>2014-01-29 23:10:54 JST STATEMENT: DROP DATABASE hoge;
>2014-01-29 23:10:54 JST ERROR: syntax error at or near ""hoge"" at character
>41
>2014-01-29 23:10:54 JST STATEMENT: UPDATE pg_database SET
>datconnlimit = e "hoge" is being accessed by other users WHERE
>datname= 'hoge';
>dropdb: database removal failed: ERROR: syntax error at or near ""hoge""
>LINE 1: UPDATE pg_database SET datconnlimit = e "hoge" is being acce...
^
>hoge=# \l
> List of databases
> Name | Owner | Encoding | Collate | Ctype | Access privileges
>-----------+----------+----------+---------+-------+-----------------------
>hoge | postgres | UTF8 | C | C |
>postgres | postgres | UTF8 | C | C |
>template0 | postgres | UTF8 | C | C | =c/postgres +
> | | | | | postgres=CTc/postgres
>template1 | postgres | UTF8 | C | C | =c/postgres +
> | | | | | postgres=CTc/postgres
>hoge database is not dropped yet.
>Is this the bug? or not?
It is a bug, sorry for doubling your work. I have updated the patch.
Regards
On Wednesday, January 29, 2014 8:50 PM, Robert Haas <robertmh...@gmail.com>
wrote:
On Wed, Jan 29, 2014 at 4:56 AM, salah jubeh <s_ju...@yahoo.com> wrote:
>>I'm not particularly in favor of implementing this as client-side
>>functionality, because then it's only available to people who use that
>>particular client. Simon Riggs proposed a server-side option to the
>>DROP DATABASE command some time ago, and I think that might be the way
>>to go.
>
> Could you please direct me -if possible- to the thread. I think,implementing
> it on the client side gives the user the some visibility and control.
> Initially, I wanted to force drop the database, then I have changed it to
> kill connections. I think the change in the client tool, is simple and
> covers the main reason for not being able to drop a database. I think,
> killing client connection is one of the FAQs.
>
> OTOH, having an option like "DROP DATABASE [IF EXISTS, FORCE] database" is
> more crisp. However, what does "force" mean? many options exist such as
> killing the connections gently, waiting for connections to terminate,
> killing connections immediately. Also, as Alvaro Herrera mentioned, DROP
> OWNED BY and/or REASSIGNED OWNED BY might hinder the force option -an
> example here would be nice-. So, for quick wins, I prefer the kill option in
> the client side; but, for mature solution , certainly back-end is the way to
> proceed.
http://www.postgresql.org/message-id/1296552979.1779.8622.camel@ebony
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/src/bin/scripts/dropdb.c b/src/bin/scripts/dropdb.c
index fa6ea3e..9041caa 100644
--- a/src/bin/scripts/dropdb.c
+++ b/src/bin/scripts/dropdb.c
@@ -32,6 +32,7 @@ main(int argc, char *argv[])
{"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 @@ main(int argc, char *argv[])
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 @@ 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:wWeik", long_options, &optindex)) != -1)
{
switch (c)
{
@@ -84,6 +87,9 @@ main(int argc, char *argv[])
case 'i':
interactive = true;
break;
+ case 'k':
+ kill = true;
+ break;
case 0:
/* this covers the long options */
break;
@@ -121,8 +127,6 @@ main(int argc, char *argv[])
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,6 +135,51 @@ main(int argc, char *argv[])
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 a cleanup in case of dropdb fail */
+ if (PQntuples(result) == 1)
+ {
+ database_conn_limit = malloc (1 + strlen(PQgetvalue(result, 0, 0)));
+ strcpy (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);
@@ -138,6 +187,16 @@ main(int argc, char *argv[])
{
fprintf(stderr, _("%s: database removal failed: %s"),
progname, PQerrorMessage(conn));
+ /* Cleanup: reset the datconnlimit value to the original value */
+ if (kill)
+ {
+ resetPQExpBuffer(&sql);
+ appendPQExpBuffer(&sql, "UPDATE pg_database SET datconnlimit=%s WHERE datname='%s';\n",
+ database_conn_limit,fmtId(dbname));
+ if (echo)
+ printf("%s", sql.data);
+ result = PQexec(conn, sql.data);
+ }
PQfinish(conn);
exit(1);
}
@@ -159,6 +218,7 @@ help(const char *progname)
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
