Hello,
Version 8.4 has brought a very useful function : pg_terminate_backend()
There have been many reports since years about idle processes remaining on the
server while clients are no longer connected. While this may be due to poor
application code not closing connections correctly, it does happen that a
regular call to close an open connection fails SILENTLY for whatever reason
(poor network I/O, router buffers full, p2p on the network). This can impair
server resources seriously without being noticed. (I have seen same situation
with Oracle too).
pg_terminate_backend() allows to explicitly kill a process through a pl/pgsql
function in a client application. The following does it somehow rightly :
create or replace function suicide() returns void as $$
declare
res boolean;
p integer;
begin
select pg_backend_pid() into p;
set log_min_error_statement = PANIC;
set log_min_messages = PANIC;
select pg_terminate_backend(p) into res;
set log_min_messages = WARNING;
set log_min_error_statement = ERROR;
end;
$$ language plpgsql security definer;
My request is as follows : that superuser privileges be unnecessary to call
the function, that when called by an unprivileged user, it does not raise an
error situation and that no error is logged. After all, a user is allowed to
close his connection.
Please consider.
Thank you.