Status of plperl inter-sp calling

From: Tim Bunce <Tim(dot)Bunce(at)pobox(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Tim Bunce <Tim(dot)Bunce(at)pobox(dot)com>
Subject: Status of plperl inter-sp calling
Date: 2009-12-30 22:54:30
Message-ID: 20091230225430.GA29510@timac.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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) returns text 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, \(at)_)->(@_);
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 as a 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.)

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kevin Grittner 2009-12-30 23:08:04 A third lock method
Previous Message Hiroshi Inoue 2009-12-30 22:50:00 Re: krb_server_keyfile setting doesn't work on Windows