Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group