Hello Sawada,
>- This patch is not patched to master branch
Sorry, My mistake
>//new connections are not allowed
Corrected.
I hope now the patch in better state, if somthing left, I will be glad to fix
it
Regards
On Tuesday, January 28, 2014 4:17 AM, Sawada Masahiko <sawada.m...@gmail.com>
wrote:
On 2014年1月17日 0:56, salah jubeh <s_ju...@yahoo.com> wrote:
>
>>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.
>
Thank you for the patch.
And sorry for delay in reviewing.
I started to look this patch, So the following is first review comment.
- This patch is not patched to master branch
I tried to patch this patch file to master branch, but I got following error.
$ cd postgresql
$ patch -d. -p1 < ../dropdb.patch
can't find fiel to patch at input line 3
Perhaps you used the wrong -p or --strip option?
the text leading up to this was:
------------------------------
|--- dropdb_org.c 2014-01-16
|+++ dropdb.c 2014-01-16
------------------------------
There is not dropdb_org.c. I think that you made mistake when the
patch is created.
- This patch is not according the coding rule
For example, line 71 of the patch:
//new connections are not allowed
It should be:
/* new connections are not allowed */
(Comment blocks that need specific line breaks should be formatted as
block comments, where the comment starts as /*------.)
Please refer to coding rule.
<http://wiki.postgresql.org/wiki/Developer_FAQ#What.27s_the_formatting_style_used_in_PostgreSQL_source_code.3F>
Regards,
-------
Sawada Masahiko
diff --git a/src/bin/scripts/dropdb.c b/src/bin/scripts/dropdb.c
index fa6ea3e..755abf4 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,11 +135,64 @@ 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 = 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 the datconnlimit value to the original value */
+ 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 @@ 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
