[HACKERS] pl/perl thoughts

[Andrew Dunstan]

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.




---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend