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

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