Add force option to dropdb

Hello,

I think adding a force option to dropdb is usefull for many purposes, mainly; scripting. For example, in a test enviroment, I restore a daily backup of production databases. The newly created databases are used as templates for development and also for reading, thus replacing them is completly safe.

I uses dropdb and pg_terminate_backend  for this purpose, since some users might forget to close connections.
I have had a look on dropdb.c and I have amended the code to also terminate client connections using pg_terminate_backend

So the functionallity of the dropdb if force option is used is as follows:
1. Terminate client connections using pg_terminate_backend
2. Drop the database

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?

3. Should I write two patches one for pg_version>=9.2 and one for pg_version<9.2 

Regards

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

>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 <alvherre(at)2ndquadrant(dot)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

On 2014年1月17日 0:56, salah jubeh <s_jubeh(at)yahoo(dot)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
> <alvherre(at)2ndquadrant(dot)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

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(dot)mshk(at)gmail(dot)com> wrote:

On 2014年1月17日 0:56, salah jubeh <s_jubeh(at)yahoo(dot)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
> <alvherre(at)2ndquadrant(dot)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

On Tue, Jan 28, 2014 at 9:01 AM, salah jubeh <s_jubeh(at)yahoo(dot)com> wrote:
> 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

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hello Robert,

>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.

Regards

On Tue, Jan 28, 2014 at 11:01 PM, salah jubeh <s_jubeh(at)yahoo(dot)com> wrote:
> 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(dot)mshk(at)gmail(dot)com> wrote:
> On 2014年1月17日 0:56, salah jubeh <s_jubeh(at)yahoo(dot)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
>> <alvherre(at)2ndquadrant(dot)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>
>
>

Thank you for updating patch!

It did not works fine with following case.

---
$ 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?

Regards,

-------
Sawada Masahiko

On Wed, Jan 29, 2014 at 4:56 AM, salah jubeh <s_jubeh(at)yahoo(dot)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

>$ 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 <robertmhaas(at)gmail(dot)com> wrote:

On Wed, Jan 29, 2014 at 4:56 AM, salah jubeh <s_jubeh(at)yahoo(dot)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

On Fri, Jan 31, 2014 at 9:09 AM, salah jubeh <s_jubeh(at)yahoo(dot)com> wrote:
>>$ 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.

In case it wasn't clear before, I think that a client-side hack like
this has zero chance of being acceptable to the community, and we
should mark this patch rejected. I'm not saying it couldn't be useful
to someone, but the scripts are intended to be thin wrappers around
the underlying database functionality, and I think this is straying
too far from that core mission.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hello Robert,
>but the scripts are intended to be thin wrappers around
>the underlying database functionality, and I think this is straying
>too far from that core mission.

I think, you have a  good point here.
Regards

On Friday, January 31, 2014 4:47 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

On Fri, Jan 31, 2014 at 9:09 AM, salah jubeh <s_jubeh(at)yahoo(dot)com> wrote:
>>$ 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.

In case it wasn't clear before, I think that a client-side hack like
this has zero chance of being acceptable to the community, and we
should mark this patch rejected.  I'm not saying it couldn't be useful
to someone, but the scripts are intended to be thin wrappers around
the underlying database functionality, and I think this is straying
too far from that core mission.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company