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!