Re: Status of plperl inter-sp calling

On Thu, Dec 31, 2009 at 09:47:24AM -0800, David E. Wheeler wrote:
> On Dec 30, 2009, at 2:54 PM, Tim Bunce wrote:
>
> > 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?

It doesn't at the moment. I think IMMUTABLE, STABLE and VOLATILE can be
(documented as being) ignored in this context.
Supporting STRICT probably wouldn't be too hard.

> And what does it do if the function called is not actually a Perl function?

(See "fallback-to-SQL" two paragraphs below)

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

The act of calling the function via spi_exec_query will load it, and
thereby cache it in the perl interpreter as a side effect (if the
language is the is the same: e.g., plperlu->plperlu).

> > 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.)

The underlying issue here is perl's lack of strong typing.
See http://search.cpan.org/~mlehmann/JSON-XS-2.26/XS.pm#PERL_-%3E_JSON
especially the "simple scalars" section and "used as string" example.

As far as I can see there's no way for perl to support the kind of
rich type polymorphism that PostgreSQL offers via the kind of "make it
look like a perl function call" interface that we're discussing.

[I can envisage a more complex interface where you ask for a code ref to
a sub with a specific type signature and then use that code ref to make the
call. Ah, I've just had a better idea but it needs a little more thought.
I'll send a another email later.]

> > * 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?

No reason, assuming spi_exec_prepared handles array refs properly
[I was just doing "simplest thing that could possibly work" at this stage]

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

So you're suggesting SP::foo(...) _always_ executes foo(...) via bunch
of spi_* calls. Umm. I thought performance was a major driving factor.
Sounds like you're more keen on syntactic sugar.

Tim.