Thread: Drop database / database in use question
I have some installation tools which in some cases need to replace a postgres database. For this example, assume my database name is 'fred'. Also, assume that no other processes are accessing this database. Any database access will come from my running installer. First, after validating some passwords, my installer will run this command: psql -c "select datname from pg_stat_activity where datname='fred'" -U username -d template1 I then scan the output looking for 'fred'. My (perhaps incorrect) assumption is that if I don't find the database name 'fred' in the output, then I can assume that no process is using this database, and I should be able to drop it. Later, my installer runs this code: psql -c "drop database fred" -U username -d template1 99% of the time, everything works fine. 1% of the time, the drop fails, and I get this: ERROR: database "fred" is being accessed by other users My two part question is why, and what can I do about it? At a minimum, I need to be able to reliably determine if I can drop the database. Ideally, I would like to be able to drop the database even though it is still in use - force an override of some sort - kick out the offending user. Thanks for any advice.... Dan
"Dan Armbrust" <daniel.armbrust.list@gmail.com> writes:
> First, after validating some passwords, my installer will run this command:
> psql -c "select datname from pg_stat_activity where datname='fred'" -U
> username -d template1
> I then scan the output looking for 'fred'. My (perhaps incorrect)
> assumption is that if I don't find the database name 'fred' in the
> output, then I can assume that no process is using this database, and
> I should be able to drop it.
There are obvious race conditions in that assumption. Why don't you
just try the drop and see if it succeeds?
regards, tom lane
> There are obvious race conditions in that assumption. Why don't you > just try the drop and see if it succeeds? > > regards, tom lane > I don't follow - why is there a race condition? I'm driving the commands into postgresql via the command line. The command that does the query on the pg_stat_activity table happens quite a while before my attempt to drop the table - and it's logging into the template1 database, rather than the database I want to drop. The drop attempt comes later, in a subsequent psql command line invocation. The drop command also logs in using the template1 database. Does the psql command line client connection not get cleaned up immediately, or something like that? No other command or tool will access this database (to my knowledge) in between the two commands. So what is the mystery user that I'm finding using the table? My only guess so far is that it was the autovac daemon - but I don't know enough about how that works to know if that is even a reasonable guess. Due to the nature of the installer tool I'm driving this fun, parsing back the output of the psql commands isn't much fun... and there are cases where a failure is acceptable (the database already doesn't exist, etc). If I can have a reliable drop command that always works, it would be much easier. Thanks, Dan
On Fri, Oct 17, 2008 at 8:11 AM, Dan Armbrust <daniel.armbrust.list@gmail.com> wrote: >> There are obvious race conditions in that assumption. Why don't you >> just try the drop and see if it succeeds? >> >> regards, tom lane >> > > I don't follow - why is there a race condition? I'm driving the > commands into postgresql via the command line. User 1: select * from pg_stat_activity where datname='db123'; User 2: psql db123 User 1: drop database db123;
But there is no user2. I _know_ I am the only user of this database. So how can User 1 create a race condition by himself? Or is this something PostgreSQL is doing internally (like vacuum) ? Do I really just have to keep trying the DROP command N times in a row, until it decides it wants to work? That really doesn't seem right. Thanks, Dan On Fri, Oct 17, 2008 at 9:24 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Fri, Oct 17, 2008 at 8:11 AM, Dan Armbrust > <daniel.armbrust.list@gmail.com> wrote: >>> There are obvious race conditions in that assumption. Why don't you >>> just try the drop and see if it succeeds? >>> >>> regards, tom lane >>> >> >> I don't follow - why is there a race condition? I'm driving the >> commands into postgresql via the command line. > > User 1: select * from pg_stat_activity where datname='db123'; > User 2: psql db123 > User 1: drop database db123; >
On Fri, Oct 17, 2008 at 8:28 AM, Dan Armbrust <daniel.armbrust.list@gmail.com> wrote: > But there is no user2. I _know_ I am the only user of this database. > > So how can User 1 create a race condition by himself? > > Or is this something PostgreSQL is doing internally (like vacuum) ? > > Do I really just have to keep trying the DROP command N times in a > row, until it decides it wants to work? That really doesn't seem > right. Could be autovac. Easiest way is to use psql -c to get a list of pids that are connected and kill the backends (just kill, not kill -9) then try to drop it again. loop through that every few seconds and it should work.
Dan Armbrust escribió: > Do I really just have to keep trying the DROP command N times in a > row, until it decides it wants to work? That really doesn't seem > right. Yes. Most of the time it'll work on the first try, but if you add the retry loop your operation is guaranteed to work all the time. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
I don't suppose that there is any easy way way that I can stop and/or disable the Autovac temporarily for the database that I want to drop. The only thing that I have seen so far, is that I would have to add rows to the pg_autovacuum table for each table in my database, but I'm not confident that that would even stop it from accessing the database, since it says that even when set to disabled, autovacuum will still run to prevent transaction id wraparounds, so it seems that it still would make make a quick check into the database that my drop command could collide with.
Dan Armbrust escribió: > I don't suppose that there is any easy way way that I can stop and/or > disable the Autovac temporarily for the database that I want to drop. > > The only thing that I have seen so far, is that I would have to add > rows to the pg_autovacuum table for each table in my database, but > I'm not confident that that would even stop it from accessing the > database, since it says that even when set to disabled, autovacuum > will still run to prevent transaction id wraparounds, so it seems that > it still would make make a quick check into the database that my drop > command could collide with. You seem to want to go to a lot of trouble just to a void a simple retry loop. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
It would seem that way. But if you have ever tried programming with the constraints of an InstallAnywhere installer, you would know why :) On Fri, Oct 17, 2008 at 10:43 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Dan Armbrust escribió: >> I don't suppose that there is any easy way way that I can stop and/or >> disable the Autovac temporarily for the database that I want to drop. >> >> The only thing that I have seen so far, is that I would have to add >> rows to the pg_autovacuum table for each table in my database, but >> I'm not confident that that would even stop it from accessing the >> database, since it says that even when set to disabled, autovacuum >> will still run to prevent transaction id wraparounds, so it seems that >> it still would make make a quick check into the database that my drop >> command could collide with. > > You seem to want to go to a lot of trouble just to a void a simple retry > loop. > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support >
On Fri, Oct 17, 2008 at 9:16 AM, Dan Armbrust <daniel.armbrust.list@gmail.com> wrote: > I don't suppose that there is any easy way way that I can stop and/or > disable the Autovac temporarily for the database that I want to drop. > > The only thing that I have seen so far, is that I would have to add > rows to the pg_autovacuum table for each table in my database, but > I'm not confident that that would even stop it from accessing the > database, since it says that even when set to disabled, autovacuum > will still run to prevent transaction id wraparounds, so it seems that > it still would make make a quick check into the database that my drop > command could collide with. Just set autovacuum = off in postgresql.conf and reload and it's off. -- When fascism comes to America, it will be draped in a flag and carrying a cross - Sinclair Lewis
This is interesting. On windows, if I attempt to drop a database when I am purposefully holding a connection open to the DB, it fails immediately - ~ 100 ms. On linux, if I do the same thing, the drop database command takes upwards of 4 seconds before it gives up, and says it can't drop the DB. Is this expected? Is there some way I can control how long it will block the drop command, waiting for the DB to not be in use? Thanks, Dan
"Dan Armbrust" <daniel.armbrust.list@gmail.com> writes:
> On windows, if I attempt to drop a database when I am purposefully
> holding a connection open to the DB, it fails immediately - ~ 100 ms.
> On linux, if I do the same thing, the drop database command takes
> upwards of 4 seconds before it gives up, and says it can't drop the
> DB.
> Is this expected?
I think you are comparing different PG versions. Recent releases will
delay a bit to see if the competing connection disappears.
> Is there some way I can control how long it will
> block the drop command, waiting for the DB to not be in use?
No.
regards, tom lane
On Friday 17 October 2008 11:55:19 Dan Armbrust wrote: > It would seem that way. But if you have ever tried programming with > the constraints of an InstallAnywhere installer, you would know why :) > if you are the only user, force a restart into single user mode, then drop the database, and restart normally. -- Robert Treat http://www.omniti.com Database: Scalability: Consulting