From: Kuba Ouhrabka <kuba@comgate.cz>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-performance@postgresql.org
Subject: Re: cached plans in plpgsql
Date: Fri, 21 Oct 2005 09:33:17 +0200
Message-ID: <435899BD.3070808@comgate.cz> (raw)
In-Reply-To: <11634.1129827054@sss.pgh.pa.us>
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD640@Herge.rcsinc.local>
<4357C4E8.6010602@comgate.cz>
<11634.1129827054@sss.pgh.pa.us>
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:recompile.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';
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kuba@comgate.cz, tgl@sss.pgh.pa.us
Subject: Re: cached plans in plpgsql
In-Reply-To: <435899BD.3070808@comgate.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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