pl/perl thoughts - Mailing list pgsql-hackers

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