Re: drop database command blocking other connections

"Jim Buttafuoco" <jim(at)contactbda(dot)com> writes:
> from time to time I have to drop a very large database (1TB+). The drop database command takes a long time to complete
> while its deleting the files. During this time, no one can connect to the database server, ps displays "startup
> waiting". This is with Postgresql 7.4. Has this been addressed in 8.1, if not, does anyone have some ideas on how to
> speed this up.

No, it'll probably behave the same in CVS HEAD. The problem is that
DROP DATABASE has to lock out new connections to the victim database,
and the mechanism it's using for that is a table-level lock on
pg_database, rather than something specific to one database. So
new connections to *all* DBs in the cluster will be blocked while
DROP DATABASE runs.

It strikes me that we could fix this by taking out special locks on the
database as an object (using LockSharedObject) instead of relying on
locking pg_database. There wasn't any locktag convention that'd work
for that back in 7.4, but it surely seems doable now.

regards, tom lane