Please make it easy to drop a database that is in use

On Tue, Jun 19, 2012 at 1:40 PM, Evan Martin
<postgresql@realityexists.net> wrote:

When I'm developing against a PostgreSQL database I often drop and re-create
it and I often find that the drop fails, because it's "in use by other
users". This is really annoying, especially when I know full well there are
no other users - it's just me.

Just connect another (say postgres) database and disconnect the
database you are trying to delete. And keep it in mind.

hassle, for something that should be a very simple operation. (I'm not even
writing SQL for it normally, just pressing Delete in pgAdmin.) Secondly,

So I think this proposal/issue should be sent not to PG development
team but to pgAdmin's one. Clients software should make all this
re-connections accordingly to its own rules.

pg_terminate_backend requires superuser rights. If I'm not a superuser, but
I am the owner of the database, it doesn't seem right that another user
should be able to prevent me from dropping my database.

I'd really like to see PostgreSQL directly support dropping a database,
regardless of who is using it - something like "DROP DATABASE ... CASCADE".
(Although "CASCADE" wouldn't be the appropriate word here. Maybe "DROP
DATABASE ... TO_HELL_WITH_USERS"?)

Evan

--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204

Like I said in my original post, I understand the workaround. I just
think that:

1) The workaround requires extra work for each developer (or at least
each client application) using PostgreSQL, while a fix in PostgreSQL
would solve this once and for all.
2) The workaround requires superuser privileges, which I don't think
should be required to drop your own database.

Regards,

Evan

On Thu, Jun 21, 2012 at 2:03 PM, Evan Martin
<postgresql@realityexists.net> wrote:

1) The workaround requires extra work for each developer (or at least each
client application) using PostgreSQL, while a fix in PostgreSQL would solve
this once and for all.

It is not clean what database you need to reconnect automatically
after the dropping. Moreover you may not have permissions to connect
other databases.

2) The workaround requires superuser privileges, which I don't think should
be required to drop your own database.

It does not require it. You might also be an owner to drop the database.

Regards,

Evan

On 20/06/2012 10:51 PM, Sergey Konoplev wrote:

On Tue, Jun 19, 2012 at 1:40 PM, Evan Martin
<postgresql@realityexists.net> wrote:

When I'm developing against a PostgreSQL database I often drop and
re-create
it and I often find that the drop fails, because it's "in use by other
users". This is really annoying, especially when I know full well there
are
no other users - it's just me.

Just connect another (say postgres) database and disconnect the
database you are trying to delete. And keep it in mind.

hassle, for something that should be a very simple operation. (I'm not
even
writing SQL for it normally, just pressing Delete in pgAdmin.) Secondly,

So I think this proposal/issue should be sent not to PG development
team but to pgAdmin's one. Clients software should make all this
re-connections accordingly to its own rules.

pg_terminate_backend requires superuser rights. If I'm not a superuser,
but
I am the owner of the database, it doesn't seem right that another user
should be able to prevent me from dropping my database.

I'd really like to see PostgreSQL directly support dropping a database,
regardless of who is using it - something like "DROP DATABASE ...
CASCADE".
(Although "CASCADE" wouldn't be the appropriate word here. Maybe "DROP
DATABASE ... TO_HELL_WITH_USERS"?)

Evan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sergey Konoplev

a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204

On 21/06/2012 10:20 PM, Sergey Konoplev wrote:

On Thu, Jun 21, 2012 at 2:03 PM, Evan Martin
<postgresql@realityexists.net> wrote:

1) The workaround requires extra work for each developer (or at least each
client application) using PostgreSQL, while a fix in PostgreSQL would solve
this once and for all.

It is not clean what database you need to reconnect automatically
after the dropping. Moreover you may not have permissions to connect
other databases.

That's a fair point, so perhaps DROP DATABASE should still fail if the
/current/ connection is to that database (preferably with a helpful
error like "you cannot drop the database you are connected to"). There
should be an easy way to close all /other/ connections to it, though.

2) The workaround requires superuser privileges, which I don't think should
be required to drop your own database.

It does not require it. You might also be an owner to drop the database.

It does when I try it:

SELECT pg_terminate_backend(procpid)
FROM pg_stat_activity
WHERE datname = 'dropme';

ERROR: must be superuser to signal other server processes

In this case the user was the owner of "dropme", but another user was
also connected to it. I believe that should not stop the owner from
dropping their database.

Regards,

Evan

SELECT pg_terminate_backend(procpid)
FROM pg_stat_activity
WHERE datname = 'dropme';

ERROR: must be superuser to signal other server processes

You can try this approach.

http://archives.postgresql.org/pgsql-general/2012-04/msg00100.php

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Evan Martin wrote:

Like I said in my original post, I understand the workaround. I just
think that:

1) The workaround requires extra work for each developer (or at least
each client application) using PostgreSQL, while a fix in PostgreSQL
would solve this once and for all.

For a developer it should not be a problem to write an
auxiliary function that kills the connections and drops the
database.

2) The workaround requires superuser privileges, which I don't think
should be required to drop your own database.

You said that such a feature would be useful for developers.
Developers usually have superuser privileges.
PostgreSQL 9.3 will probably allow you to pg_terminate_backend()
your own sessions even if you are not a superuser, at least there
is such a patch in the queue.

I don't think that saving a few keystrokes for lazy developers
is a good enough reason for such a thing in core.

Yours,
Laurenz Albe

Evan Martin <postgresql@realityexists.net> writes:

That's a fair point, so perhaps DROP DATABASE should still fail if the
/current/ connection is to that database (preferably with a helpful
error like "you cannot drop the database you are connected to").

It does that.

SELECT pg_terminate_backend(procpid)
FROM pg_stat_activity
WHERE datname = 'dropme';
ERROR: must be superuser to signal other server processes

As far as that goes, there's a pending patch to reduce the privileges
required to use pg_terminate_backend. I'm not in favor of having DROP
DATABASE do it for you though --- that just seems like a very
large-caliber foot gun.

regards, tom lane

On 22/06/2012 12:07 AM, Tom Lane wrote:

SELECT pg_terminate_backend(procpid)
FROM pg_stat_activity
WHERE datname = 'dropme';
ERROR: must be superuser to signal other server processes

As far as that goes, there's a pending patch to reduce the privileges
required to use pg_terminate_backend. I'm not in favor of having DROP
DATABASE do it for you though --- that just seems like a very
large-caliber foot gun.

Good to hear there's a patch coming for that. Just to be clear, I'm not
suggesting DROP DATABASE should do that by default. Basically, I'm
looking for the "--force" option here - a way to say "I know what I'm
doing, just drop this database if at all possible".

"rm -rf" is a foot gun, too, but if that -f wasn't there you would have
to write commands to set permissions on files when you couldn't care
less about the permissions and just want to delete them. Sure, a
competent Linux user could write such a command, but imagine how
annoying it would be to do that all the time! Not to mention the extra
room for errors in that command. To me, DROP DATABASE is a very similar
case.

Regards,

Evan