Thread: Drop database / database in use question

Drop database / database in use question

From
"Dan Armbrust"
Date:
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

Re: Drop database / database in use question

From
Tom Lane
Date:
"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

Re: Drop database / database in use question

From
"Dan Armbrust"
Date:
> 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

Re: Drop database / database in use question

From
"Scott Marlowe"
Date:
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;

Re: Drop database / database in use question

From
"Dan Armbrust"
Date:
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;
>

Re: Drop database / database in use question

From
"Scott Marlowe"
Date:
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.

Re: Drop database / database in use question

From
Alvaro Herrera
Date:
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.

Re: Drop database / database in use question

From
"Dan Armbrust"
Date:
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.

Re: Drop database / database in use question

From
Alvaro Herrera
Date:
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

Re: Drop database / database in use question

From
"Dan Armbrust"
Date:
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
>

Re: Drop database / database in use question

From
"Scott Marlowe"
Date:
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

Re: Drop database / database in use question

From
"Dan Armbrust"
Date:
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

Re: Drop database / database in use question

From
Tom Lane
Date:
"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

Re: Drop database / database in use question

From
Robert Treat
Date:
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