Skip site navigation (1) Skip section navigation (2)

Re: Ye olde drop-the-database-you-just-left problem

From: Brian Hurt <bhurt(at)janestcapital(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Ye olde drop-the-database-you-just-left problem
Date: 2007-05-30 17:36:14
Message-ID: 465DB60E.40806@janestcapital.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane wrote:

>I just finished giving someone the standard advice to wait a bit before
>trying to drop a database that'd just been accessed:
>http://archives.postgresql.org/pgsql-general/2007-05/msg01505.php
>
>AFAICT a "real" fix for this would involve making PQfinish() synchronous
>(don't return till backend is dead), which doesn't seem like a great
>idea.  However, it suddenly struck me that we could probably make most
>of the problem go away if we put that same wait into DROP DATABASE
>itself --- that is, if we see other backends in the target DB, sleep
>for a second or two and then recheck before erroring out.
>
>This isn't bulletproof since under high load the other backend might
>not get to quit, but it'd surely reduce the frequency of complaints
>a great deal.  And we could take out the ad-hoc sleeps that are done
>in (eg) the contrib regression tests.
>
>Thoughts?
>  
>

Is this a synchronization issue?  I'm wondering if there isn't a better 
solution.  The problem with waiting is that a) you're going to be 
waiting a lot when it's not necessary, and b) the likelyhood you won't 
wait long enough (especially under load, as you mentioned). 

I'm wondering if something like this would work.  When a backend 
connects to the database, it increments a semaphore associated with that 
database.  The last thing it does when exiting is release the semaphore- 
which is the backend's way of saying "OK, all done here".  The drop 
database command checks the semaphore- if it still has a non-zero count, 
it fails rather than dropping the database.  A possibly optional 
argument would have it wait until the semaphore is 0, and then drop the 
database.  This has the advantage of only waiting long enough.

No idea how practical this would be, tho...

Brian

In response to

Responses

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2007-05-30 17:41:05
Subject: Re: TOAST usage setting
Previous:From: Tom LaneDate: 2007-05-30 17:35:43
Subject: Re: Ye olde drop-the-database-you-just-left problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group