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

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?

regards, tom lane

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?

An option could be to add a PQfinishWait() API call, and have psql use
this one when passed a special commandline argument (which if I
understood right this guys "commerercial alternative" had). It might be
useful in other cases as well, but I can't really think of one right now :-)

//Magnus

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> Tom Lane wrote:
>> 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.

> An option could be to add a PQfinishWait() API call, and have psql use
> this one when passed a special commandline argument (which if I
> understood right this guys "commerercial alternative" had). It might be
> useful in other cases as well, but I can't really think of one right now :-)

The trouble with trying to fix this on the client side is that it's not
fixed unless every client behaves that way (all the time). Otherwise
we'll still be hearing the same complaints. "Use this magic little
option on the previous connection" isn't a user-friendly answer.

regards, tom lane

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

Brian Hurt <bhurt(at)janestcapital(dot)com> writes:
> 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

> Is this a synchronization issue?

The problem is that the user thinks his previous disconnect is finished
when it may not be --- it's entirely possible in fact that his old
backend hasn't even received the disconnect message yet. So I don't
think it's possible to rely on there being a state change inside the
database indicating that the other guy is about to exit.

Even if we had a semaphore of the sort you suggest, I doubt people would
want DROP DATABASE to wait indefinitely. The real question here is how
long is it reasonable for DROP DATABASE to wait before failing ...

regards, tom lane

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> 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.

Is there any way to tell, perhaps from the command string, that the process is
about to start exiting? What stage of exiting is it that we think the kernel
goes to lunch?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Tom,

> Even if we had a semaphore of the sort you suggest, I doubt people would
> want DROP DATABASE to wait indefinitely. The real question here is how
> long is it reasonable for DROP DATABASE to wait before failing ...

10 to 15 seconds, I'd say. Is that going to be long enough for backends to
release, assuming the DB isn't under extreme load?

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> Is there any way to tell, perhaps from the command string, that the process is
> about to start exiting? What stage of exiting is it that we think the kernel
> goes to lunch?

I haven't really done any detailed investigation, but I would think that
a simple process exit (when there's not temp tables to drop or anything
like that) should complete within one scheduler timeslice. That would
mean that when this problem occurs, it's usually because the kernel hasn't
scheduled the backend at all since the disconnect message was sent;
which in turn means there is no way at all to know that the backend is
going to exit when it does get a chance to run.

regards, tom lane

> > 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.

Yup, waiting in drop database up to 10-30 secs would imho be fine.

Andreas

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> Even if we had a semaphore of the sort you suggest, I doubt people would
>> want DROP DATABASE to wait indefinitely. The real question here is how
>> long is it reasonable for DROP DATABASE to wait before failing ...

> 10 to 15 seconds, I'd say. Is that going to be long enough for backends to
> release, assuming the DB isn't under extreme load?

While testing this, 10 seconds seemed too long --- more than long enough
for someone to start thinking it's broken. I settled on 5 seconds which
seemed about the edge of the threshold of pain. Our experience with the
buildfarm suggests that 1 second is usually long enough (since that's
the delay we were using in the contrib regression tests, and they don't
fail often on this), so I think it'll be all right at 5.

regards, tom lane

On May 31, 2007, at 1:32 AM, Zeugswetter Andreas ADI SD wrote:
>>> 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.
>
> Yup, waiting in drop database up to 10-30 secs would imho be fine.

Even 10 seconds seems rather long, doesn't it? You'd have to have an
awfully busy system to need to wait more than like 5 seconds for the
closing backend to get scheduled, and it'd be rather ugly to force
someone to wait 30 seconds just to find out that someone's still
connected to the database.

How about starting with 5 seconds and seeing if that takes care of
most situations?
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Jim Nasby <decibel(at)decibel(dot)org> writes:
> How about starting with 5 seconds and seeing if that takes care of
> most situations?

Yeah, I came to that same conclusion ...
http://archives.postgresql.org/pgsql-hackers/2007-06/msg00029.php

regards, tom lane