Drop database / database in use question

"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 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:

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

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

"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