pl/perl thoughts

Hackers,

FYI here are some of my thoughts I just sent to Joshua Drake.

I would add a couple of points:
. since there is a security advisory for Safe.pm prior to version 2.08,
maybe we should replace "require Safe;" with "use Safe 2.08;".
. I thought about some sort of precompilation methods for perl functions
known to the database (store the function refs in a hash so you could
get at them via syntax like
&{$func_hash{schema_name}->{func_name}}(@func_args) ). I decided to
abandon this approach mainly because of postgresql's function
overloading, making disambiguation very hard. With a full SPI interface
there will be access to any function, not just plperl functions, and
postgres will do the disambiguation for you. Also, see below for a
slightly more klunky but much more lightweight approach which would mean
there was no necessity for a callback to postgresql.

cheers

andrew

>
> I haven't got past the thinking stage yet, and a few things have held
> up my progress elsewhere. The one possibly productive thought I have
> to share with you is this: it is probably overkill to have a separate
> Safe container for each plperl function. I don't see any reason that
> they shouldn't all live in one Safe container. Then they could share
> data and indeed some preloaded functions without doing anything special.
>
> Thus the following perl contained in plperl.c and executed on
> interpreter startup:
>
> require Safe; SPI::bootstrap();
> sub ::mksafefunc { my $x = new Safe;
> $x->permit_only(':default');$x->permit(':base_math');
> $x->share(qw[&elog &DEBUG &LOG &INFO &NOTICE &WARNING
> &ERROR]);
> return $x->reval(qq[sub { $_[0] }]); }
> sub ::mkunsafefunc {return eval(qq[ sub { $_[0] } ]); }
>
> would become something like:
>
> require Safe; SPI::bootstrap();
> use vars qw($PLContainer); $PLContainer = new
> Safe("PLPerl");
>
> $PLContainer->permit_only(':default');$PLContainer->permit(':base_math');
> $PLContainer->share(qw[&elog &DEBUG &LOG &INFO &NOTICE
> &WARNING &ERROR]);
> sub ::mksafefunc { return $PLContainer->reval(qq[sub {
> $_[0] }]); }
> sub ::mkunsafefunc {return eval(qq[ sub { $_[0] } ]); }
>
> Now you could do something like this:
>
> create function myplperlfuncs() returns int language plperl is '
> $datavar = "foo";
> $funcvar = sub { return "bar"; };
> return 1;
> ';
>
> create function f1 () returns text language plperl as '
> return $datavar;
> ';
>
> create function f2() returns text language plperl as '
> return &$funcvar();
> ';
>
> At the start of your session you would issue "select myplperlfuncs();"
> to preload the values, and thereafter you could call f1() and f2()
> quite happily.
>
> It's actually a bit of a pity that we don't have provision for a per
> database exec on startup procedure, which could handle this more
> elegantly (i.e. we would register myplperlfuncs() as something to be
> run on startup, so the user wouldn't have to worry at all about it.)
>
> I have not tested any of this - it is still straight out of my head.
>
> As others have noted, the biggest need is for full SPI access. I don't
> think this is hard - just a fair bit of work. After that it would be
> very nice to have a DBI type handle so that programmers used to doing
> things the DBI way (and what perl programmer isn't?) will feel right
> at home with plperl. In most cases I guess that would be a thin layer
> over the SPI stuff. Alternatively, you could hide the SPI access and
> just make the DBI handle visible - it would still be mostly calling
> SPI under the hood, of course. As they say in the perl world,
> TIMTOWTDI. Providing a DBI handle would also square with what happens
> in the Java world, where you can write server side methods that access
> the database via a JDBC interface.
>
>