[BUGS] plpgsql functions crash cvs
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 0x0046f43d in CallXactCallbacks (event=XACT_EVENT_COMMIT)
at xact.c:2618
#2 0x0047138b in CommitTransaction () at xact.c:1534
#3 0x00472be7 in CommitTransactionCommand () at xact.c:2184
#4 0x0058ecde in finish_xact_command () at postgres.c:2017
#5 0x00590475 in exec_simple_query (
query_string=0x8f8f28 select admin.fn_revoke_all('public');)
at postgres.c:1041
#6 0x005917ab in PostgresMain (argc=4, argv=0x8bb360,
username=0x8bb320 jurka) at postgres.c:3231
#7 0x00566a18 in ServerLoop () at postmaster.c:2917
#8 0x00567861 in PostmasterMain (argc=3, argv=0x89b830)
at postmaster.c:980
#9 0x005250de 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
Re: [BUGS] plpgsql functions crash cvs
Kris Jurka wrote:
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:
Interesting. 8.1 also crashes. 8.0 instead gives this output:
psql:/home/alvherre/funcs.sql:86: ERROR: control reached end of function
without RETURN
CONTEXT: PL/pgSQL function fn_revoke_all_functions_from
SQL statement SELECT admin.fn_revoke_all_functions_from( $1 , $2 )
PL/pgSQL function fn_revoke_all line 11 at perform
psql:/home/alvherre/funcs.sql:88: ERROR: control reached end of function
without RETURN
CONTEXT: PL/pgSQL function fn_revoke_all_functions_from
SQL statement SELECT admin.fn_revoke_all_functions_from( $1 , $2 )
PL/pgSQL function fn_revoke_all line 11 at perform
psql:/home/alvherre/funcs.sql:89: ERROR: control reached end of function
without RETURN
CONTEXT: PL/pgSQL function fn_revoke_all_functions_from
SQL statement SELECT admin.fn_revoke_all_functions_from( $1 , $2 )
PL/pgSQL function fn_revoke_all line 11 at perform
psql:/home/alvherre/funcs.sql:90: ERROR: control reached end of function
without RETURN
CONTEXT: PL/pgSQL function fn_revoke_all_functions_from
SQL statement SELECT admin.fn_revoke_all_functions_from( $1 , $2 )
PL/pgSQL function fn_revoke_all line 11 at perform
In 8.1, the backtrace reads thus:
(gdb) bt
#0 plpgsql_xact_cb (event=XACT_EVENT_COMMIT, arg=0x0)
at /pgsql/source/81_rel/src/pl/plpgsql/src/pl_exec.c:4525
#1 0x080b37ed in CallXactCallbacks (event=XACT_EVENT_COMMIT)
at /pgsql/source/81_rel/src/backend/access/transam/xact.c:2618
#2 0x080b5923 in CommitTransaction ()
at /pgsql/source/81_rel/src/backend/access/transam/xact.c:1534
#3 0x080b73d9 in CommitTransactionCommand ()
at /pgsql/source/81_rel/src/backend/access/transam/xact.c:2184
#4 0x081e2d8d in finish_xact_command ()
at /pgsql/source/81_rel/src/backend/tcop/postgres.c:2006
#5 0x081e40c5 in exec_simple_query (
query_string=0x83d011c SELECT admin.fn_revoke_all('public');)
at /pgsql/source/81_rel/src/backend/tcop/postgres.c:1032
#6 0x081e593d in PostgresMain (argc=4, argv=0x8372898,
username=0x8372860 alvherre)
at /pgsql/source/81_rel/src/backend/tcop/postgres.c:3217
#7 0x081b771a in ServerLoop ()
at /pgsql/source/81_rel/src/backend/postmaster/postmaster.c:2853
#8 0x081b88d4 in PostmasterMain (argc=1, argv=0x8371860)
at /pgsql/source/81_rel/src/backend/postmaster/postmaster.c:941
#9 0x081753c9 in main (argc=1, argv=0x8371860)
at /pgsql/source/81_rel/src/backend/main/main.c:265
(gdb) info locals
expr = (PLpgSQL_expr *) 0x7f7f7f7f
enext = value optimized out
So the problem seems to be that the context containing the PLpgSQL_expr
was reset too early. I'll investigate more after dinner, if somebody
doesn't beat me to it.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] plpgsql functions crash cvs
Kris Jurka [EMAIL PROTECTED] writes: 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 I believe this is new as of 8.1. The problem is that we are chasing a list threaded through plpgsql parse structures, and since Neil's cleanup of plpgsql space management about a year ago, those structures are no longer guaranteed to remain in existence throughout a backend's run. In particular, modifying the definition of a function (in this example, by doing a REVOKE on it) when it's been used earlier in the same transaction can lead to a dangling pointer being chased at transaction end. On reflection I don't see any need for the explicit list. We are using it just to help keep track of whether simple expressions have been initialized in the current transaction. It'd be better to store the value of GetTopTransactionId into a simple expression when we init it, and then assume it's OK as long as this matches. Thanks for the report! regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
