Re: Status of plperl inter-sp calling

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, \(at)_)->(@_);
> 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 use a cached query? That way, one gets the benefit of polymorphism. Maybe there's a SP package that does caching, and an SPI package 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 using polymorphic 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