Thread: Waiting for Disconnect

Hi All,

I have some tests that create a database, run a bunch of tests against
it, and then drop it. But I was running into an issue where I'd get
this error even after I disconnected from the test database:

   ERROR:  source database "foo" is being accessed by other users

And no, no other users are connecting to the database, just me. Some
time ago, someone on IRC pointed me to doing a select in the
pg_stat_activity table, and that seemed to work:

         sleep 1 while $dbh->selectrow_array(
             'SELECT 1 FROM pg_stat_activity where datname = ?',
             undef, 'foo'
         );

However, I found that I could get the above error even after this query
returns no rows if I'm running my tests on a really fast box. My
assumption is that the back end is deleting the record from this table
before the connection is actually fully dropped. So, to get around this
I'm both checking pg_stat_activity and trapping the error. Here's what
my code looks like:

         # Wait until the other connection has been dropped. Throw in an
extra
         # query to kill a bit of time, just to make sure that we really
are
         # fully disconnted. It seems like it sometimes thinks there are
still
         # connections even after the query returns false.
         sleep 1 while $dbh->selectrow_array(
             'SELECT 1 FROM pg_stat_activity where datname = ?',
             undef, $self->{conf}{pg}{db_name}
         );

         for (my $i = 0; $i < 5; $i++) {
             # This might fail a couple of times as we wait for the
database
             # connection to really drop. It might be sometime *after*
the above
             # query returns false!
             eval { $dbh->do(qq{DROP DATABASE
"$self->{conf}{pg}{db_name}"}) };
             if (my $err = $@) {
                 die $err
                   if $i >= 5 || $err !~ /is being accessed by other
users/;
                 sleep 1, next;
             }
             last;
         }

So, my question is, is this the only reasonable approach to resolving
this issue? I'm running these tests against 8.0.x and 7.4.x.

Thanks,

David

PS: Please Cc me on all replies, as I am not actually on the
pgsql-general list. Thanks!

David Wheeler <david@kineticode.com> writes:
> I have some tests that create a database, run a bunch of tests against
> it, and then drop it. But I was running into an issue where I'd get
> this error even after I disconnected from the test database:

>    ERROR:  source database "foo" is being accessed by other users

Yeah, I've seen this too.  The problem is that the backend you were
using hasn't exited yet when you try to issue the DROP DATABASE.
This is a bit surprising given the small amount of work normally
needed for a backend to exit.  (If it has to drop temp tables or
something, that might be another story, but I've seen it happen
with no such cleanup work needed.)

As best I can tell, it happens because the kernel has simply given
no, none, zero cycles to the orphaned backend, preferring instead
to run your shell, psql, and the postmaster.  I suppose this is
because some scheduler heuristic inside the kernel has decided that
the backend is a "background" process that deserves low priority.
I get the impression that some platforms are more prone to this than
others, which is consistent with the idea that it's a scheduler issue.

The only real solution I know of is to sleep for a little before
trying to issue the DROP DATABASE.  Certainly, adding more database
traffic as you suggest isn't going to improve matters.

            regards, tom lane

On Apr 21, 2005, at 11:16 PM, Tom Lane wrote:

> The only real solution I know of is to sleep for a little before
> trying to issue the DROP DATABASE.  Certainly, adding more database
> traffic as you suggest isn't going to improve matters.

I think just sleeping is all I'm doing. The extra database traffic I'm
adding is via a different connection to a different database
(template1).

Thanks for the reply, Tom. At least I know that I've done what I can
for this issue in my app.

Regards,

David