Re: drop database regardless of connections

Kev wrote:

> So I tried to do this in Perl, but for some reason neither
> kill() nor Win32::Process::KillProcess() actually terminate the
> threads.

Threads? Each backend is a distinct process. I haven't the foggiest why
they might be ignoring the signal, but then I'm very far from clueful
about Pg on win32.

Anyway, you can use `psql' to query the activity tables using something
like "SELECT procpid FROM pg_stat_activity WHERE datname = 'dbtodrop'"
and see which backend pids need to be killed, then use 'pg_ctl kill
signame pid' to kill them. A bit of powershell, cmd.exe, etc should do
the job, though I agree that for win32 a builtin "pg_kill_backend()"
function would be nicer, in that you could just execute a query like:

SELECT pg_kill_backend(procpid)
FROM pg_stat_activity
WHERE datname = 'dbtodrop';

You can use pg_cancel_backend() to cancel queries, but there doesn't
seem to be an equivalent to actually disconnect / terminate a backend.

Note that you can also update the system tables to prevent new
connections being made to the database you're about to drop by setting
pg_database.datallowconn to 'f' for the DB in question. That way, while
you're killing off backends you won't have more joining.

I'm curious about why you need to drop and create so many databases that
this is an issue, though.

--
Craig Ringer