Re: Autovacuum Daemon Disrupting dropdb?

Tom Lane wrote:
> "Thomas F. O'Connell" <tfo(at)sitening(dot)com> writes:
>> On Mar 11, 2006, at 4:13 PM, Tom Lane wrote:
>>> For a "real" solution, perhaps DROP DATABASE could somehow look to
>>> determine if there's an autovac daemon active in the target database,
>>> and if so send it a SIGINT and wait for it to go away.
>
>> In general, it also seems like a --force option or something similar
>> would be reasonable for dropdb because the state of the database in
>> terms of user activity wouldn't seem to matter a whole lot if the
>> intent is to drop it.
>
> ... except to the processes connected to it.
>
> If we trusted selective SIGTERM we could imagine sending that to
> non-autovac processes connected to the target database, but we don't
> really. In any case, killing a database that has active users seems
> like a pretty large-caliber foot-gun to me; that condition suggests
> *very* strongly that the database is not so idle as all that.

I would find this useful. We have a large test suite that drops and
recreates a test database as required to maintain test isolation. Two
problems we have are:
- If a test fails to close all of its connections, the rest of
the tests are victimized as the database cannot be dropped.
- If you close all your connections and immediately attempt to drop the
database, it will often fail as it appears that PostgreSQL is still
cleaning up the recently closed connections. I don't know if this is
a PostgreSQL issue or an issue on how our database driver closes
connections (psycopg1 for Python).

To work around the first issue, we have to examine pg_stat_activity for
process ids and kill any outstanding ones.

To work around he second issue, we attempt to drop a number of times with a
short sleep between each try. Which is rather 'icky.

I have similar issues I need to deal with on our staging server, which each
day automatically needs to have the database reset with a fresh dump of our
production database, code updates rolled out and schema and data migration
patches applied.

--
Stuart Bishop <stuart(at)stuartbishop(dot)net>
http://www.stuartbishop.net/