Обсуждение: Please make it easy to drop a database that is in use
Hi All,
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.
I'm aware of the workaround: use pg_terminate_backend to kill existing connections, but there are two problems with that. Firstly, it's just a 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, 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
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.
I'm aware of the workaround: use pg_terminate_backend to kill existing connections, but there are two problems with that. Firstly, it's just a 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, 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
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 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 > >
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:
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.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.
It does when I try it: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.
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.
---
Regards,
Raghavendra
EnterpriseDB Corporation
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