pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
cached plans in plpgsql 5+ messages / 3 participants [nested] [flat]
* cached plans in plpgsql @ 2005-10-20 14:07 Kuba Ouhrabka <kuba@comgate.cz> 0 siblings, 0 replies; 5+ messages in thread From: Kuba Ouhrabka @ 2005-10-20 14:07 UTC (permalink / raw) To: pgsql-performance Hi, is there an easy way to flush all cached query plans in pl/pgsql functions? I've long running sessions where data are changing and the plans become inaccurate after a while. I can imagine something like recreating all pl/pgsql functions. I can recreate them from sql source files but I'd prefer recreating them inside the database without accessing files outside. I can think only of one way - reconstructing function source code from pg_proc and EXECUTEing it. But it's not the cleanest solution (there isn't saved the actual source code anywhere so there could be problems with quoting etc.). Can you see any other possibility? How do you solve this problem? [And yes, I don't want to re-connect...] Thanks, Kuba ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cached plans in plpgsql @ 2005-10-20 15:03 Merlin Moncure <merlin.moncure@rcsonline.com> 0 siblings, 1 reply; 5+ messages in thread From: Merlin Moncure @ 2005-10-20 15:03 UTC (permalink / raw) To: Kuba Ouhrabka <kuba@comgate.cz>; +Cc: pgsql-performance Kuba wrote: > is there an easy way to flush all cached query plans in pl/pgsql > functions? I've long running sessions where data are changing and the > plans become inaccurate after a while. I can imagine something like > recreating all pl/pgsql functions. I can recreate them from sql source > files but I'd prefer recreating them inside the database without > accessing files outside. I can think only of one way - reconstructing > function source code from pg_proc and EXECUTEing it. But it's not the > cleanest solution (there isn't saved the actual source code anywhere so > there could be problems with quoting etc.). Can you see any other > possibility? How do you solve this problem? [And yes, I don't want to > re-connect...] Start here: http://archives.postgresql.org/pgsql-hackers/2005-09/msg00690.php Merlin ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cached plans in plpgsql @ 2005-10-20 16:25 Kuba Ouhrabka <kuba@comgate.cz> parent: Merlin Moncure <merlin.moncure@rcsonline.com> 0 siblings, 1 reply; 5+ messages in thread From: Kuba Ouhrabka @ 2005-10-20 16:25 UTC (permalink / raw) To: Merlin Moncure <merlin.moncure@rcsonline.com> > [howto recreate plpgsql functions] > > Start here: > http://archives.postgresql.org/pgsql-hackers/2005-09/msg00690.php Great, thanks! I slighltly modified the function - it was not working for overloaded functions (same name, different arguments) and for functions with named arguments. Modified version attached for anyone interested - not perfect but works for me... Kuba Attachments:^ permalink raw reply [nested|flat] 5+ messages in threadrecompile.sql (text/plain, 1.9K) download
CREATE OR REPLACE FUNCTION recompile_function(a_oid oid) RETURNS INTEGER AS $func$ DECLARE Par_proc TEXT; Var_datos RECORD; Var_codigo text; Var_args varchar; Var_nameArg varchar; Var_nameRet varchar; i int; BEGIN SELECT proretset, prorettype, proargtypes, proargnames, prosrc, pronargs, proname INTO Var_datos FROM pg_proc WHERE oid = a_oid FOR UPDATE ; Par_proc := Var_datos.proname; SELECT typname::varchar INTO Var_nameRet FROM pg_type WHERE oid = Var_datos.prorettype; Var_codigo := 'CREATE OR REPLACE FUNCTION '||Par_proc||'('; IF Var_datos.pronargs > 0 THEN Var_args := ''; FOR i IN 0..Var_datos.pronargs-1 LOOP SELECT typname::varchar INTO Var_nameArg FROM pg_type WHERE oid = Var_datos.proargtypes[i]; Var_args := Var_args|| COALESCE(Var_datos.proargnames[i+1], '') || ' ' || Var_nameArg||', '; END LOOP; Var_codigo := Var_codigo||RTRIM(Var_args,', '); END IF; if Var_datos.proretset THEN Var_codigo := Var_codigo||') RETURNS SETOF '||Var_nameRet||' AS'''; ELSE Var_codigo := Var_codigo||') RETURNS '||Var_nameRet||' AS'''; END IF; Var_codigo := Var_codigo|| replace(Var_datos.prosrc,'''' , '\''''); Var_codigo := Var_codigo||'''LANGUAGE ''plpgsql'''; EXECUTE(Var_codigo); RETURN 0; END; $func$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION recompile_all_functions() RETURNS INTEGER AS $func$ DECLARE lr_rec RECORD; li_x INTEGER; BEGIN FOR lr_rec IN SELECT p.oid as oid FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace LEFT JOIN pg_language l ON l.oid = p.prolang WHERE NOT p.proisagg AND pg_catalog.pg_function_is_visible(p.oid) AND n.nspname != 'pg_catalog' AND NOT p.proname IN ('recompile_all_functions', 'recompile_function') AND l.lanname = 'plpgsql' LOOP li_x := recompile_function(lr_rec.oid); END LOOP; RETURN 0; END; $func$ LANGUAGE 'plpgsql';
* Re: cached plans in plpgsql @ 2005-10-20 16:50 Tom Lane <tgl@sss.pgh.pa.us> parent: Kuba Ouhrabka <kuba@comgate.cz> 0 siblings, 1 reply; 5+ messages in thread From: Tom Lane @ 2005-10-20 16:50 UTC (permalink / raw) To: Kuba Ouhrabka <kuba@comgate.cz>; +Cc: Merlin Moncure <merlin.moncure@rcsonline.com>; pgsql-performance Kuba Ouhrabka <kuba@comgate.cz> writes: > IF Var_datos.pronargs > 0 THEN > Var_args := ''; > FOR i IN 0..Var_datos.pronargs-1 LOOP > SELECT typname::varchar INTO Var_nameArg FROM pg_type WHERE oid = Var_datos.proargtypes[i]; > Var_args := Var_args|| COALESCE(Var_datos.proargnames[i+1], '') || ' ' || Var_nameArg||', '; > END LOOP; This will not work at all; it makes far too many incorrect assumptions, like proargnames always being non-null and having subscripts that match proargtypes. (It'll mess things up completely for anything that has OUT arguments, too.) It's pretty much the hard way to form a function reference anyway --- you can just cast the function OID to regprocedure, which aside from avoiding a lot of subtle assumptions about the catalog contents, will deal with schema naming issues, something the above likewise fails at. To avoid having to reconstruct argument names/types, I'd suggest using an ALTER FUNCTION command instead of CREATE OR REPLACE FUNCTION, maybe DECLARE fullproname text := a_oid::regprocedure; ... EXECUTE 'ALTER FUNCTION ' || fullproname || ' RENAME TO ' || Var_datos.proname; regards, tom lane ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cached plans in plpgsql @ 2005-10-21 07:33 Kuba Ouhrabka <kuba@comgate.cz> parent: Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Kuba Ouhrabka @ 2005-10-21 07:33 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance Tom, many thanks. Perfect advice as usual... Corrected version attached for the archives. Kuba Tom Lane napsal(a): > Kuba Ouhrabka <kuba@comgate.cz> writes: > >> IF Var_datos.pronargs > 0 THEN >> Var_args := ''; >> FOR i IN 0..Var_datos.pronargs-1 LOOP >> SELECT typname::varchar INTO Var_nameArg FROM pg_type WHERE oid = Var_datos.proargtypes[i]; > > > >> Var_args := Var_args|| COALESCE(Var_datos.proargnames[i+1], '') || ' ' || Var_nameArg||', '; >> END LOOP; > > > This will not work at all; it makes far too many incorrect assumptions, > like proargnames always being non-null and having subscripts that match > proargtypes. (It'll mess things up completely for anything that has OUT > arguments, too.) > > It's pretty much the hard way to form a function reference anyway --- > you can just cast the function OID to regprocedure, which aside from > avoiding a lot of subtle assumptions about the catalog contents, > will deal with schema naming issues, something the above likewise > fails at. > > To avoid having to reconstruct argument names/types, I'd suggest using > an ALTER FUNCTION command instead of CREATE OR REPLACE FUNCTION, maybe > > DECLARE fullproname text := a_oid::regprocedure; > ... > EXECUTE 'ALTER FUNCTION ' || fullproname || ' RENAME TO ' || Var_datos.proname; > > regards, tom lane Attachments:^ permalink raw reply [nested|flat] 5+ messages in threadrecompile.sql (text/plain, 1.1K) download
CREATE OR REPLACE FUNCTION recompile_all_functions() RETURNS INTEGER AS $func$ DECLARE lr_rec RECORD; li_x INTEGER; BEGIN FOR lr_rec IN SELECT p.oid as oid FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace LEFT JOIN pg_language l ON l.oid = p.prolang WHERE NOT p.proisagg AND pg_catalog.pg_function_is_visible(p.oid) AND n.nspname != 'pg_catalog' AND NOT p.proname IN ('recompile_all_functions', 'recompile_function') AND l.lanname = 'plpgsql' LOOP li_x := recompile_function(lr_rec.oid); END LOOP; RETURN 0; END; $func$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION recompile_function(a_oid oid) RETURNS INTEGER AS $func$ DECLARE lv_name TEXT; lv_fullname TEXT; BEGIN SELECT INTO lv_name proname FROM pg_proc WHERE oid = a_oid ; lv_fullname := a_oid::regprocedure; EXECUTE 'ALTER FUNCTION ' || lv_fullname || ' RENAME TO ugly_function_name'; lv_fullname := a_oid::regprocedure; EXECUTE 'ALTER FUNCTION ' || lv_fullname || ' RENAME TO ' || lv_name; RETURN 0; END; $func$ LANGUAGE 'plpgsql';
end of thread, other threads:[~2005-10-21 07:33 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-10-20 14:07 cached plans in plpgsql Kuba Ouhrabka <kuba@comgate.cz> 2005-10-20 15:03 Re: cached plans in plpgsql Merlin Moncure <merlin.moncure@rcsonline.com> 2005-10-20 16:25 ` Kuba Ouhrabka <kuba@comgate.cz> 2005-10-20 16:50 ` Tom Lane <tgl@sss.pgh.pa.us> 2005-10-21 07:33 ` Kuba Ouhrabka <kuba@comgate.cz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox