Re: DROP DATABASE always seeing database in use

"Russell Smith" <mr-russ@pws.com.au> writes:

> Gregory Stark wrote:
>> It seems there's something wrong with CheckOtherDBBackends() but I haven't
>> exactly figured out what. There are no other sessions but drop database keeps
>> saying "regression" is being accessed by other users. I do see Autovacuum
>> touching tables in regression but CheckOtherDBBackends() is supposed to send
>> it a sigkill if it finds it and it doesn't seem to be doing so.
>>
>> I've been hacking on unrelated stuff in this database and have caused multiple
>> core dumps and autovacuum is finding orphaned temp tables. It's possible some
>> state is corrupted in some way here but I don't see what.
>
> Autovacuum does this as well.  I know on 8.1, I've been bitten by it a
> number of times.  I don't know for CVS or newer version than 8.1.  But
> it's an option worth considering as autovac doesn't show up in
> pg_stat_activity.

In 8.3 autovacuum politely steps out of the way if it's holding up traffic
(actually anyone who gets stuck behind vacuum just rudely shoots it in the
back). So this *shouldn't* happen any more which is why I was raising it.

However it was solved earlier by someone else. It was a a prepared
transaction. Which was precisely what my comment about "some state is
corrupted" meant. In this case the server had core dumped after preparing a
transaction and that prepared transaction was blocking the DROP DATABASE.

8.4 will now print a better message specifically pointing out the prepared
transactions for the next hapless DBA to be caught in this situation.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about
EnterpriseDB'sPostgreSQL training!