Обсуждение: Request about pg_terminate_backend()

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.

On Tue, Jul 28, 2009 at 1:19 PM, Saleem EDAH-TALLY<nmset@netcourrier.com> wrote:
> 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.

Well, if someone wants that, they can always write a security definer
function, just as you did.  But if we made that available to everyone,
then we'd get complaints from people who DON'T want it that way, and
those people wouldn't have an out.

...Robert