Hi everyone,
I'd like to drop a database to which users have idle connections. I'm
running pgsql 8.2 on win32, soon 8.3, using mod_perl2 and
connect_cached.
I know this issue has been brought up several times over the past few
years, but I don't quite understand the reason for it not being
supported as an sql command or command-line utility.
Over on pgsql.hackers Stuart Bishop posted this code:
> CREATE OR REPLACE FUNCTION _killall_backends(text)
> RETURNS Boolean AS $$
> import os
> from signal import SIGTERM
>
> plan = plpy.prepare(
> "SELECT procpid FROM pg_stat_activity WHERE datname=$1",
['text']
> )
> success = True
> for row in plpy.execute(plan, args):
> try:
> plpy.info("Killing %d" % row['procpid'])
> os.kill(row['procpid'], SIGTERM)
> except OSError:
> success = False
>
> return success
> $$ LANGUAGE plpythonu;
However, I'd like to avoid installing Python just for this task, if
possible. So I tried to do this in Perl, but for some reason neither
kill() nor Win32::Process::KillProcess() actually terminate the
threads. I also tried clearing CachedKids, but that doesn't seem to
have any effect. So even with no client apps running, I am unable to
drop the database without restarting the whole database service.
Does this mean I have to run a separate database server for each
database just to be able to boot people off a particular database
before dropping it? It seems like a basic thing to be able to drop a
database unconditionally without restarting the service for people who
are using other databases on the same service. Maybe I'm missing
something that everyone else who asked about this eventually realized?
Thanks,
Kev
