Thread

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2007-05-30T17:09:35Z

   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
   
   

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

   Magnus Hagander <magnus@hagander.net> — 2007-05-30T17:18:02Z

   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
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2007-05-30T17:35:43Z

   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
   
   

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

   Brian Hurt <bhurt@janestcapital.com> — 2007-05-30T17:36:14Z

   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
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2007-05-30T18:01:44Z

   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
   
   

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

   Gregory Stark <stark@enterprisedb.com> — 2007-05-30T19:59:06Z

   "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
   
   
   

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

   Josh Berkus <josh@agliodbs.com> — 2007-05-30T20:03:10Z

   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
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2007-05-30T20:27:40Z

   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
   
   

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

   Zeugswetter Andreas DCP SD <zeugswettera@spardat.at> — 2007-05-31T08:32:18Z

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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2007-06-01T19:41:59Z

    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
    
    

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

    Decibel! <decibel@decibel.org> — 2007-06-01T23:42:24Z

    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)
    
    
    
    

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2007-06-02T05:15:44Z

    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