eKol in #postgresql reported a problem with a plpgsql function crashing
the server. I tested the attached against 8.2cvs as of this morning
and got this stacktrace:
#0 plpgsql_xact_cb (event=XACT_EVENT_COMMIT, arg=0x0) at pl_exec.c:4521
#1 0x000000000046f43d in CallXactCallbacks (event=XACT_EVENT_COMMIT)
at xact.c:2618
#2 0x000000000047138b in CommitTransaction () at xact.c:1534
#3 0x0000000000472be7 in CommitTransactionCommand () at xact.c:2184
#4 0x000000000058ecde in finish_xact_command () at postgres.c:2017
#5 0x0000000000590475 in exec_simple_query (
query_string=0x8f8f28 "select admin.fn_revoke_all('public');")
at postgres.c:1041
#6 0x00000000005917ab in PostgresMain (argc=4, argv=0x8bb360,
username=0x8bb320 "jurka") at postgres.c:3231
#7 0x0000000000566a18 in ServerLoop () at postmaster.c:2917
#8 0x0000000000567861 in PostmasterMain (argc=3, argv=0x89b830)
at postmaster.c:980
#9 0x00000000005250de in main (argc=3, argv=0x3e8) at main.c:254
Kris JurkaDROP SCHEMA admin CASCADE;
CREATE SCHEMA admin;
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(text)
returns void as $$
declare
v_user alias for $1;
v_schema record;
v_obj record;
v_current_db text;
begin
-- Second, revoke on functions, tables, and views from
-- user schemas.
for v_schema in select nspname AS name FROM pg_namespace WHERE nspname NOT
LIKE 'pg%' AND nspname NOT LIKE 'info%'
loop
perform admin.fn_revoke_all_functions_from(v_user, v_schema.name);
end loop;
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;
SELECT admin.fn_revoke_all('public');
-- Sometimes doesn't crash on the first attempt
SELECT admin.fn_revoke_all('public');
SELECT admin.fn_revoke_all('public');
SELECT admin.fn_revoke_all('public');
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
