Re: Status of plperl inter-sp calling - Mailing list pgsql-hackers

On Dec 30, 2009, at 2:54 PM, Tim Bunce wrote:

> That handles the arity of the calls and invokes the right SP, bypassing
> SQL if the SP is already loaded.

Nice.

> That much works currently. Behind the scenes, when a stored procedure is
> loaded into plperl the code ref for the perl sub is stored in a cache.
> Effectively just
>    $cache{$name}[$nargs] = $coderef;
> An SP::AUTOLOAD sub intercepts any SP::* call and effectively does
>    lookup_sp($name, \@_)->(@_);
> For SPs that are already loaded lookup_sp returns $cache{$name}[$nargs]
> so the overhead of the call is very small.

Definite benefit, there. How does it handle the difference between IMMUTABLE | STABLE | VOLATILE, as well as STRICT
functions?And what does it do if the function called is not actually a Perl function? 

> For SPs that are not cached, lookup_sp returns a code ref of a closure
> that will invoke $name with the args in @_ via
>    spi_exec_query("select * from $name($encoded_args)");
>
> The fallback-to-SQL behaviour neatly handles non-cached SPs (forcing
> them to be loaded and thus cached), and inter-language calling (both
> plperl<->plperl and other PLs).

Is there a way for such a function to be cached? If not, I'm not sure where cached functions come from.

> Limitations:
>
> * It's not meant to handle type polymorphism, only the number of args.

Well, spi_exec_query() handles the type polymorphism. So might it be possible to call SP::function() and have it not
usea cached query? That way, one gets the benefit of polymorphism. Maybe there's a SP package that does caching, and an
SPIpackage that does not? (Better named, though.) 

> * When invoked via SQL, because the SP isn't cached, all non-ref args
>  are all expressed as strings via quote_nullable(). Any array refs
>  are encoded as ARRAY[...] via encode_array_constructor().

Hrm. Why not use spi_prepare() and let spi_exec_prepared() handle the quoting?

> I don't see either of those as significant issues: "If you need more
> control for a particular SP then don't use SP::* to call that SP."

If there was a non-cached version that was essentially just sugar for the SPI stuff, I think that would be more
predicable,no? I'm not saying there shouldn't be a cached interface, just that it should not be the first choice when
usingpolymorphic functions and non-PL/Perl functions. 

> Open issues:
>
> * What should SP::foo(...) return? The plain as-if-called-by-perl
>  return value, or something closer to what spi_exec_query() returns?

The former.

> * If the called SP::foo(...) calls return_next those rows are returned
>  directly to the client.  That can be construed as a feature.

As a list?

Best,

David