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

While waiting for feedback on my earlier plperl refactor and feature
patches I'm working on a further patch that adds, among other things,
fast inter-plperl-sp calling.

I want to outline what I've got and get some feedback on open issues.

To make a call to a stored procedure from plperl you just call the
function name prefixed by SP::. For example:
   create function poly() returns text language plperl       as $$ return "poly0" $$;   create function poly(text)
returnstext language plperl       as $$ return "poly1" $$   create function poly(text, text) returns text language
plperl      as $$ return "poly2" $$
 
   create function foo() returns text language plperl as $$       SP::poly();       SP::poly(1);       SP::poly(1,2);
   return undef;   $$
 

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

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.

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

Limitations:

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

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

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

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?
 

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

* Cache invalidation. How can I hook into an SP being dropped so I can pro-actively invalidate the cache?

* Probably many other things I've not thought of.

This is all a little rough and exploratory at the moment.
I'm very keen to get any feedback you might have.

Tim.

p.s. Happy New Year! (I may be off-line for a few days.)