The following bug has been logged online:

Bug reference: 2297
Logged by: bernd
Email address: bernd (AT) tti (DOT) hn
PostgreSQL version: 8.1.3
Operating system: Linux Fedora Core 3
Description: plpgsql function causes disconnect sometimes
Details:

I'm trying to make some admin functions that I can use to revoke privileges
from users. The functions are all stored in a schema called admin. Here are
the functions:

-------------------------------------------------------
create or replace function admin.fn_show_functions(text)
returns setof text as $$
declare
v_schema alias for $1;
v_schema_oid oid;
v_function pg_catalog.pg_proc%rowtype;
v_function_arg text;
v_function_name_and_args text;
begin
select into v_schema_oid oid
from pg_catalog.pg_namespace
where nspname = v_schema;

if found then
for v_function in select * from pg_catalog.pg_proc
where pronamespace = v_schema_oid
loop
v_function_name_and_args := v_function.proname || '(';

for i in 0..(v_function.pronargs - 1)
loop
select into v_function_arg typname
from pg_catalog.pg_type
where oid = v_function.proargtypes[i];

if v_function_arg is not null then
v_function_name_and_args := v_function_name_and_args ||
v_function_arg || ', ';
end if;
end loop;

v_function_name_and_args := trim(trailing ', ' from
v_function_name_and_args);
v_function_name_and_args := v_function_name_and_args || ')';

return next v_function_name_and_args;
end loop;
end if;

return;
end;
$$
language plpgsql;
-------------------------------------------------------
create or replace function admin.fn_revoke_all_functions_from(text, text)
returns void as $$
declare
v_user alias for $1;
v_schema alias for $2;
v_obj record;
begin
for v_obj in select * from admin.fn_show_functions(v_schema) as
name
loop
raise notice 'revoking function %', v_obj.name;
execute 'revoke all on function ' ||
quote_ident(v_schema) || '."' ||
replace(v_obj.name, '(', '"(') || ' from ' ||
quote_ident(v_user);
end loop;
end;
$$
language plpgsql;
-------------------------------------------------------
create or replace function admin.fn_revoke_all(text)
returns void as $$
declare
v_user alias for $1;
v_schema record;
v_obj record;
v_current_db text;
begin
for v_schema in select * from admin.fn_show_user_schemas() as name
loop
raise notice 'revoking all functions from % in %', v_user,
v_schema.name;
perform admin.fn_revoke_all_functions_from(v_user, v_schema.name);
end loop;
end;
$$
language plpgsql;
-------------------------------------------------------
create or replace function admin.fn_show_user_schemas()
returns setof text as $$
declare
v_schema pg_catalog.pg_namespace%rowtype;
begin
for v_schema in select * from pg_catalog.pg_namespace
where not (nspname like 'pg_%' or nspname like 'information_schema')
loop
return next v_schema.nspname;
end loop;
end;
$$
language plpgsql;
-------------------------------------------------------

When I execute:

SELECT * FROM admin.fn_revoke_all('someuser');

I sometimes get disconnected with this message:

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

and in the log I have:

LOG: server process (PID 16202) was terminated by signal 11
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2006-03-02 10:50:01 CST
LOG: checkpoint record is at 0/D004564
LOG: redo record is at 0/D004564; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 26852; next OID: 101599
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: connection received: host=[local]
FATAL: the database system is starting up
LOG: redo starts at 0/D0045A8
LOG: record with zero length at 0/D037BCC
LOG: redo done at 0/D037BA4
LOG: database system is ready

Since I have some RAISE NOTICE statements, I can see that the function
fn_revoke_all disconnects me right after it finishes revoking on all the
functions.

I have reproduced this behaviour on another machine with PostgreSQL 8.1.1
running CentOS 3. It doesn't matter which database I use, the result is the
same.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Thu, 2 Mar 2006, bernd wrote:

I reported this here:
http://archives.postgresql.org/pgsql...3/msg00006.php

and it was fixed here:
http://archives.postgresql.org/pgsql...3/msg00021.php

This will be in 8.1.4 when released.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster