Обсуждение: cached plans in plpgsql

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

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

 > [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
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

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