Waiting for Disconnect

[David Wheeler <david@xxxxxxxxxxxxxx>]

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!


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings