I’m working with PostgreSQL for my day job, and liking it.
We’re fairly heavy users of stored procedures implemented in PL/Perl, with ~10,000 lines in ~100 functions (some of which have bloated to painful proportions). This creates some interesting issues and challenges for us.
There’s a window of opportunity now to make improvements to PL/Perl for PostgreSQL 8.5. I’m planning to work with Andrew Dunstan to agree on a set of changes and develop the patches.
As a first step along that road I want to map out here the changes I’m thinking of and to ask for comments and suggestions.
- Enable modular programming by pre-loading user libraries.
- Soften the hard choice between plperl and plperlu, so there’s less reason to “give up” and use plperlu.
- Improve performance.
- Improve flexibility for future changes.
- Enable use of tracing/debugging tools.
- Enable configuration of perl at initialization
- Configure extra items to be shared with the Safe compartment
- Permit some more opcodes in the Safe compartment
- Execute END blocks at process end
- Name PL/Perl functions
- Miscellaneous updates to the PL/Perl documentation
- Improve Performance
Add ability to specify in postgresql.conf some code to be run when a perl interpreter is initialized. For example:
plperl.at_init_do = 'use lib qw(/path/to/mylib); use MyPlPerlUtils; use List::Util qw(sum);'
The Safe compartment used for plperl functions can’t access any namespace outside the compartment. So, by default, any subroutines defined by libraries loaded via
plperl.at_init_do won’t be callable from plperl functions.
Some mechanism is needed to specify which extra subroutines, and/or variables, should be shared with the Safe compartment. For example:
plperl.safe_share = '$foo, myfunc, sum'
I’d like to add the following opcodes to the set of opcodes permitted in the Safe compartment:
caller, dbstate, tms.
Currently PostgreSQL doesn’t execute END blocks when the backend postgres process exits (oddly, it actually executes them immediately after initializing the interpreter). Fixing that would greatly simplify use of tools like NYTProf that need to know when the interpreter is exiting. Updated: used to say “at server shutdown” which was wrong.
Currently PL/Perl functions are compiled as anonymous subroutines. Applying the same technique as the Sub::Name module would allow them have ‘more useful’ names than the current ‘__ANON__’.
For a PL/Perl function called “foo”, a minimal implementation would use a name like “foo__id54321” where 54321 is the oid of the function. This avoids having to deal with polymorphic functions (where multiple functions have the same name but different arguments).
The names won’t enable inter-function calling and may not even be installed in the symbol table. They’re just to improve error messages and to enable use of tools like Devel::NYTProf:: PgPLPerl (as yet unreleased).
To document the new functionality and expand/update the related text.
It seems likely that there’s room for improvement. Some code profiling is needed first, though, so I’ll leave this one vague for now.
Any comments on the above?
Anything you’d like to add?
If so, speak up, time is short!
For completeness I’ll mention that I was thinking of adding a way to permit extra opcodes (plperl.safe_permit=’caller’) and a way to use a subclass of the Safe module (plperl.safe_class=’MySafe’). I dropped them because I felt the risks of subtle security issues outweighed the benefits. Any requirements for which these proposals seem like a good fit can also be met via