BUG #2297: plpgsql function causes disconnect sometimes - Mailing list pgsql-bugs

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.