Обсуждение: Wishlist of PL/Perl Enhancements for PostgreSQL 8.5

I'm planning to work with Andrew Dunstan on some enhancements to PL/Perl
for PostgreSQL 8.5.

I've written up some initial proposals here:

  http://blog.timbunce.org/2009/10/05/wishlist-of-plperl-enhancements-for-postgresql-8-5/

I'd welcome any feedback on those, and any other suggestions you may have.
(Either here, on the blog, or direct to me - I'll post a summary.)

Thanks.

Tim.

On Mon, Oct 05, 2009 at 11:25:18PM +0100, Tim Bunce wrote:
> I'm planning to work with Andrew Dunstan on some enhancements to PL/Perl
> for PostgreSQL 8.5.
>
> I've written up some initial proposals here:
>
>   http://blog.timbunce.org/2009/10/05/wishlist-of-plperl-enhancements-for-postgresql-8-5/
>
> I'd welcome any feedback on those, and any other suggestions you may have.
> (Either here, on the blog, or direct to me - I'll post a summary.)

I've posted the bulk of the text (including some minor updates) below
to make commenting here easier.

Tim.


Goals:

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

Specific Proposals:

* Enable configuration of perl at initialization

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);'

* Configure extra items to be shared with the Safe compartment

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'

* Permit some more opcodes in the Safe compartment

I’d like to add the following opcodes to the set of opcodes permitted in
the Safe compartment: caller, dbstate, tms.

* Execute END blocks at process end

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.

* Name PL/Perl functions

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

On Tue, Oct 06, 2009 at 09:57:39AM +0100, Tim Bunce wrote:
> On Mon, Oct 05, 2009 at 11:25:18PM +0100, Tim Bunce wrote:
> > I'm planning to work with Andrew Dunstan on some enhancements to PL/Perl
> > for PostgreSQL 8.5.
> >
> > I've written up some initial proposals here:
> >
> >   http://blog.timbunce.org/2009/10/05/wishlist-of-plperl-enhancements-for-postgresql-8-5/
> >
> > I'd welcome any feedback on those, and any other suggestions you may have.
> > (Either here, on the blog, or direct to me - I'll post a summary.)
>
> I've posted the bulk of the text (including some minor updates) below
> to make commenting here easier.
>
> Tim.
>
> Goals:
>
> - 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.
>
> Specific Proposals:
>
> * Enable configuration of perl at initialization
>
> 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);'

Would there be some way to integrate this with the per-ROLE,
per-database GUC infrastructure?

> 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

Inter-function calling could be handy, too.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

* David Fetter (david@fetter.org) wrote:
> On Tue, Oct 06, 2009 at 09:57:39AM +0100, Tim Bunce wrote:
> > >   http://blog.timbunce.org/2009/10/05/wishlist-of-plperl-enhancements-for-postgresql-8-5/

Is someone working on adding the pl/perl hooks to be called as an
anonymous PG function?

    Thanks,

        Stephen

David Fetter wrote:
> On Tue, Oct 06, 2009 at 09:57:39AM +0100, Tim Bunce wrote:

> > * Enable configuration of perl at initialization
> >
> > 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);'
>
> Would there be some way to integrate this with the per-ROLE,
> per-database GUC infrastructure?

I don't have that patch installed right now but I don't think it would
change the current behavior, which is that it should work just fine (and
if it doesn't, that's a bug).

One thing that's not clear to me is how would this work for
non-superusers.  Wouldn't this violate the Safe containment?  If it does
then it should be superuser-only, no?

> > 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
>
> Inter-function calling could be handy, too.

I agree.  This would mean that the function name mangling would have to
be more predictable ... maybe using the argument types instead of OID?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Tue, Oct 06, 2009 at 09:34:52AM -0400, Alvaro Herrera wrote:
> David Fetter wrote:
> > On Tue, Oct 06, 2009 at 09:57:39AM +0100, Tim Bunce wrote:
>
> > > * Enable configuration of perl at initialization
> > >
> > > 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);'
> >
> > Would there be some way to integrate this with the per-ROLE,
> > per-database GUC infrastructure?
>
> I don't have that patch installed right now but I don't think it
> would change the current behavior, which is that it should work just
> fine (and if it doesn't, that's a bug).
>
> One thing that's not clear to me is how would this work for
> non-superusers.  Wouldn't this violate the Safe containment?  If it
> does then it should be superuser-only, no?

Setting it, sure.  I was thinking it could be handy for different
roles in different DBs to be able to have different PL/Perls :)

> > > 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
> >
> > Inter-function calling could be handy, too.
>
> I agree.  This would mean that the function name mangling would have to
> be more predictable ... maybe using the argument types instead of OID?

As Andrew Dunstan pointed out off-list, the argument type naming
scheme is quite vulnerable to naming oddities:

foo(int,text,int)
foo_int(text,int)
foo_int_text(int)
foo_int_text_int()

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter wrote:
> On Tue, Oct 06, 2009 at 09:34:52AM -0400, Alvaro Herrera wrote:
>> David Fetter wrote:
>>> On Tue, Oct 06, 2009 at 09:57:39AM +0100, Tim Bunce wrote:
>>>> The names won’t enable inter-function calling
>>> Inter-function calling could be handy, too.
>> I agree.  This would mean that the function name mangling would have to
>> be more predictable ... maybe using the argument types instead of OID?
>
> As Andrew Dunstan pointed out off-list, the argument type naming
> scheme is quite vulnerable to naming oddities:
>
> foo(int,text,int)
> foo_int(text,int)
> foo_int_text(int)
> foo_int_text_int()

Are we looking down the wrong end of the telescope here? What if we had
something more like the "C" binding for functions:

CREATE FUNCTION foo(int,text,int) AS 'MyModule::internal_foo' LANGUAGE
plperl;

If you want inter-function calls you use internal_foo.

--
  Richard Huxton
  Archonet Ltd

Filip Rembiałkowski escribió:
> 2009/10/6 Richard Huxton <dev@archonet.com>
>
> >
> > Are we looking down the wrong end of the telescope here? What if we had
> > something more like the "C" binding for functions:
> >
> > CREATE FUNCTION foo(int,text,int) AS 'MyModule::internal_foo' LANGUAGE
> > plperl;
>
> > If you want inter-function calls you use internal_foo.
> >
> this really would be a great feature for many plperl users.
> ( I'm not sure how it breaks current PL model in postgres)

This would be a pain for dumps, because the user would have to install
the modules separately.  Maybe we could have a different language (say
plperl2 for lack of a better name) that could work this way; if you
really wanted to do this, you could.

Having plperl functions as separate files would be great for stuff like
syntax coloring in editors.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Filip Rembiałkowski escribió:
>> 2009/10/6 Richard Huxton <dev@archonet.com>
>>
>>> Are we looking down the wrong end of the telescope here? What if we had
>>> something more like the "C" binding for functions:
>>>
>>> CREATE FUNCTION foo(int,text,int) AS 'MyModule::internal_foo' LANGUAGE
>>> plperl;
>>> If you want inter-function calls you use internal_foo.
>>>
>> this really would be a great feature for many plperl users.
>> ( I'm not sure how it breaks current PL model in postgres)
>
> This would be a pain for dumps, because the user would have to install
> the modules separately.  Maybe we could have a different language (say
> plperl2 for lack of a better name) that could work this way; if you
> really wanted to do this, you could.

I was assuming it would be smart enough to issue a "use MyModule" itself
(the same way the C library does). As far as pg_dump is concerned it
probably needs to be taught how to bundle external files - custom
tsearch dictionaries get left behind too.

It almost certainly would need to be call plperlmod or some such of
course - taking backwards compatibility away will just mean you have
loads of perl hackers chasing after you with sharpened onions.

--
  Richard Huxton
  Archonet Ltd

On Tue, Oct 06, 2009 at 09:18:22AM -0700, David Fetter wrote:
> On Tue, Oct 06, 2009 at 09:34:52AM -0400, Alvaro Herrera wrote:
> > David Fetter wrote:
> > > On Tue, Oct 06, 2009 at 09:57:39AM +0100, Tim Bunce wrote:
> >
> > > > * Enable configuration of perl at initialization
> > > >
> > > > 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);'
> > >
> > > Would there be some way to integrate this with the per-ROLE,
> > > per-database GUC infrastructure?
> >
> > I don't have that patch installed right now but I don't think it
> > would change the current behavior, which is that it should work just
> > fine (and if it doesn't, that's a bug).
> >
> > One thing that's not clear to me is how would this work for
> > non-superusers.  Wouldn't this violate the Safe containment?  If it
> > does then it should be superuser-only, no?

It must be superuser-only as it executes at interpreter initialization
before the Safe compartment is created.

> Setting it, sure.  I was thinking it could be handy for different
> roles in different DBs to be able to have different PL/Perls :)

It would be handy, so I'll add a separate plperl.at_safe_init_do
to specify code to execute inside the safe compartment when it's first
created. Thanks.

Tim.