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

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

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


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

From
Magnus Hagander
Date:
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


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

From
Tom Lane
Date:
Magnus Hagander <magnus@hagander.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


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

From
Brian Hurt
Date:
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


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

From
Tom Lane
Date:
Brian Hurt <bhurt@janestcapital.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


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

From
Gregory Stark
Date:
"Tom Lane" <tgl@sss.pgh.pa.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



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

From
Josh Berkus
Date:
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


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

From
Tom Lane
Date:
Gregory Stark <stark@enterprisedb.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


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

From
"Zeugswetter Andreas ADI SD"
Date:
> >  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


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

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.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


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

From
Jim Nasby
Date:
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@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)




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

From
Tom Lane
Date:
Jim Nasby <decibel@decibel.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