The following bug has been logged online:
Bug reference: 2297
Logged by: bernd
Email address: bernd@tti.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.
