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...9/msg00690.php

Merlin

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> [howto recreate plpgsql functions]
>
> Start here:
> http://archives.postgresql.org/pgsql...9/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

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';

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

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 broadcast)---------------------------
TIP 6: explain analyze is your friend