Re: polymorphic arguments and return type for PL/pgSQL

Tom Lane wrote:
> Joe Conway wrote:
>>2) create hash key using a new structure that includes function oid,
>>return type, and argument types, and use that for direct lookup.
>
> The latter. By the time you pay the price of a hash lookup, a slightly
> longer key is nearly free. (Maybe entirely free, since it might produce
> better-distributed hash values.)
>
> dynahash only supports fixed-length keys, so don't forget to zero out
> unused positions in the argument type vector.
>
> BTW, I can't see any need to include the return type in the hash key ---
> wouldn't it be predetermined given the argument types?
>

The attached implements a compiled function hash in addition to the
earlier changes to support PL/pgSQL polymorphism. It also includes the
ealier requested change wrt generating an ERROR when faced with
polymorphic arguments or return type and no FuncExpr node available.

The compiled function hash uses the following key:
typedef struct PLpgSQL_func_key
{
Oid funcOid;
Oid argtypes[FUNC_MAX_ARGS];
} PLpgSQL_func_key;

I did a simple test to check performance impact using the ealier sample
function and table:

CREATE OR REPLACE FUNCTION tst(anyelement) returns anyarray as '
begin
if $1 is of (int2, int4, int8, float4, float8, numeric) then
return array[$1 * 2];
elsif $1 is of (text) then
return array[$1 || $1];
else
return array[$1];
end if;
end;
' language 'plpgsql';

create table plpgsql(f1 int, f2 float8, f3 text, f4 oid);
insert into plpgsql values(1, 1.1, 'a', 1);
insert into plpgsql values(2, 2.2, 'b', 2);

---------------------------------------------------------
with original patch (linked list compiled function cache)
---------------------------------------------------------
psql regression
explain analyze SELECT tst(f1) from plpgsql; Total runtime: 3.73 msec
explain analyze SELECT tst(f1) from plpgsql; Total runtime: 0.19 msec
explain analyze SELECT tst(f2) from plpgsql; Total runtime: 1.89 msec
explain analyze SELECT tst(f2) from plpgsql; Total runtime: 0.19 msec
explain analyze SELECT tst(f3) from plpgsql; Total runtime: 1.36 msec
explain analyze SELECT tst(f3) from plpgsql; Total runtime: 0.19 msec
explain analyze SELECT tst(f4) from plpgsql; Total runtime: 0.70 msec
explain analyze SELECT tst(f4) from plpgsql; Total runtime: 0.21 msec
explain analyze SELECT tst(f1) from plpgsql; Total runtime: 0.18 msec
explain analyze SELECT tst(f2) from plpgsql; Total runtime: 0.19 msec
explain analyze SELECT tst(f3) from plpgsql; Total runtime: 0.18 msec
explain analyze SELECT tst(f4) from plpgsql; Total runtime: 0.18 msec
\q

----------------------------------------------------
with this patch (hash table compiled function cache)
----------------------------------------------------
psql regression
explain analyze SELECT tst(f1) from plpgsql; Total runtime: 2.93 msec
explain analyze SELECT tst(f1) from plpgsql; Total runtime: 0.19 msec
explain analyze SELECT tst(f2) from plpgsql; Total runtime: 1.64 msec
explain analyze SELECT tst(f2) from plpgsql; Total runtime: 0.18 msec
explain analyze SELECT tst(f3) from plpgsql; Total runtime: 1.05 msec
explain analyze SELECT tst(f3) from plpgsql; Total runtime: 0.19 msec
explain analyze SELECT tst(f4) from plpgsql; Total runtime: 0.69 msec
explain analyze SELECT tst(f4) from plpgsql; Total runtime: 0.19 msec
explain analyze SELECT tst(f1) from plpgsql; Total runtime: 0.19 msec
explain analyze SELECT tst(f2) from plpgsql; Total runtime: 0.18 msec
explain analyze SELECT tst(f3) from plpgsql; Total runtime: 0.21 msec
explain analyze SELECT tst(f4) from plpgsql; Total runtime: 0.22 msec
\q

No difference worth caring about. In more complex scenarios, the hash
table cache should win hands down, I'd think.

Compiles clean, and passes all regression tests. I'll look to update the
docs and regression tests as part of my post freeze array/polymorphic
function cleanup.

If there are no objections, please apply.

Thanks,

Joe