Thread: Curious plperl behavior

Ran into this switching a DBI based thing into a plperl function.  The
root cause is probably a perl variable scope thing, but still this is
very interesting behavior and may trip up others.

Given code such as this:

create or replace function plperlhell()
returns int
as $$
# prepare a plan, call a func that runs it,
# then free it.
#
# then call this again
#
# mimic use strict; but works in pl/perl
BEGIN { strict->import(); }

my $plan = spi_prepare("select version()");
elog(NOTICE, "Plan: $plan");

testfunc($plan);

spi_freeplan($plan);

$plan = "beef";

elog(NOTICE, "plan now $plan");

sub testfunc
{
    my($arg) = @_;

    elog(NOTICE, "in testfunc, plan: $plan arg: $arg");
    my $rv = spi_exec_prepared($plan);
    elog(NOTICE, "Results: $rv");
}

$$
language 'plperl';

we prepare a statement and then testfunc() is a helper which ends up
doing the actual exec'ing (in reality, after its done work on the
data).  What I ran into was on subsequent calls to the plperl func
(not testfunc()) was I'd get an spi_exec_prepared error that the plan
was missing.   When you run the above in 8.2, 8.3 or 8.4 (8.3 & 4 on
linux, 2 on osx perl verions 5.8.8 in both:


postgres=# select plperlhell();
NOTICE:  Plan: 49abf0
NOTICE:  in testfunc, plan: 49abf0 arg: 49abf0
NOTICE:  Results: HASH(0x886578)
NOTICE:  plan now beef
  plperlhell
------------

(1 row)

postgres=# select plperlhell();
NOTICE:  Plan: 49abf0
NOTICE:  in testfunc, plan: beef arg: 49abf0
ERROR:  error from Perl function: spi_exec_prepared: Invalid prepared
query passed at line 26.


Notice on the second run the plan is still "beef" when it was set to
49abf0 (which when passed as the arg is correct)
Any perl gurus have any further info on this?  It was a bit surprising
to encounter this.  I'm guessing it has something to do with variable
scope and the fact plperl funcs are just anonymous functions.

Stuffing it in $_SHARED seems to work fine and ends up with results as
one would expect.

--
Jeff Trout <jeff@jefftrout.com>
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/

On Feb 24, 2010, at 8:44 AM, Jeff wrote:
>
> Notice on the second run the plan is still "beef" when it was set to
> 49abf0 (which when passed as the arg is correct)
> Any perl gurus have any further info on this?  It was a bit
> surprising to encounter this.  I'm guessing it has something to do
> with variable scope and the fact plperl funcs are just anonymous
> functions.
>
> Stuffing it in $_SHARED seems to work fine and ends up with results
> as one would expect.
>

Thanks to RhodiumToad on irc for the pointer - posting this here for
posterity.

 From perlref:

"Thus is because named subroutines are created (and
        capture any outer lexicals) only once at compile time, whereas
anony-
        mous subroutines get to capture each time you execute the
'sub' opera-
        tor. If you are accustomed to using nested subroutines in
other pro-
        gramming languages with their own private variables, you'll
have to
        work at it a bit in Perl.  The intuitive coding of this type
of thing
        incurs mysterious warnings about "will not stay shared".  For
example,
        this won't work:

            sub outer {
                my $x = $_[0] + 35;
                sub inner { return $x * 19 }   # WRONG
                return $x + inner();
            }

        A work-around is the following:

            sub outer {
                my $x = $_[0] + 35;
                local *inner = sub { return $x * 19 };



                return $x + inner();
            }

        Now inner() can only be called from within outer(), because of
the tem-
        porary assignments of the closure (anonymous subroutine).  But
when it
        does, it has normal access to the lexical variable $x from the
scope of
        outer().


--
Jeff Trout <jeff@jefftrout.com>
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/

Jeff <threshar@threshar.is-a-geek.com> writes:
> [ oracular excerpt from perlref ]

So is this just a dark corner of Perl, or is plperl doing something to
help you get confused?  In particular, do we need to add anything to
the plperl documentation?  We're not trying to explain Perl to people,
but if plperl is doing something that contributes to this, maybe it
requires documentation.

            regards, tom lane

On 24/02/10 20:55, Tom Lane wrote:
> Jeff<threshar@threshar.is-a-geek.com>  writes:
>> [ oracular excerpt from perlref ]
>
> So is this just a dark corner of Perl, or is plperl doing something to
> help you get confused?  In particular, do we need to add anything to
> the plperl documentation?  We're not trying to explain Perl to people,
> but if plperl is doing something that contributes to this, maybe it
> requires documentation.

It is documented.

http://www.postgresql.org/docs/8.4/static/plperl-funcs.html

     Note:  The use of named nested subroutines is dangerous in Perl,
especially if they refer to lexical variables in the enclosing scope.
Because a PL/Perl function is wrapped in a subroutine, any named
subroutine you create will be nested. In general, it is far safer to
create anonymous subroutines which you call via a coderef. See the
perldiag man page for more details.


There's two ways to read that:
1. "Dangerous in Perl" - well, what isn't?
2. "Dangerous in Perl" - blimey, if they think it's dangerous, it must
make lion-wrestling safe.

--
   Richard Huxton
   Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> On 24/02/10 20:55, Tom Lane wrote:
>> but if plperl is doing something that contributes to this, maybe it
>> requires documentation.

> It is documented.

> http://www.postgresql.org/docs/8.4/static/plperl-funcs.html

>      Note:  The use of named nested subroutines is dangerous in Perl,
> especially if they refer to lexical variables in the enclosing scope.
> Because a PL/Perl function is wrapped in a subroutine, any named
> subroutine you create will be nested. In general, it is far safer to
> create anonymous subroutines which you call via a coderef. See the
> perldiag man page for more details.

Hmm.  Jeff found some relevant material on perlref.  Should that link be
added?  Should the link(s) be more specific than telling you to read the
whole d*mn man page?  Neither of those pages are short, and each contains
a wealth of material that isn't related to this issue.

            regards, tom lane

On 24/02/10 21:34, Tom Lane wrote:
> Richard Huxton<dev@archonet.com>  writes:
>> On 24/02/10 20:55, Tom Lane wrote:
>>> but if plperl is doing something that contributes to this, maybe it
>>> requires documentation.
>
>> It is documented.
>
>> http://www.postgresql.org/docs/8.4/static/plperl-funcs.html

> Hmm.  Jeff found some relevant material on perlref.  Should that link be
> added?  Should the link(s) be more specific than telling you to read the
> whole d*mn man page?  Neither of those pages are short, and each contains
> a wealth of material that isn't related to this issue.

Hmm - perhaps a suggestion to google for "perl nested named subroutine".
That seems to give a set of relevant results. Includes perldiag,
perlref, our mailing lists and Apache's mod_perl (which makes sense).

--
   Richard Huxton
   Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> On 24/02/10 21:34, Tom Lane wrote:
>> Hmm.  Jeff found some relevant material on perlref.  Should that link be
>> added?  Should the link(s) be more specific than telling you to read the
>> whole d*mn man page?  Neither of those pages are short, and each contains
>> a wealth of material that isn't related to this issue.

> Hmm - perhaps a suggestion to google for "perl nested named subroutine".
> That seems to give a set of relevant results. Includes perldiag,
> perlref, our mailing lists and Apache's mod_perl (which makes sense).

Seems like a reasonable idea to me --- any objections?  We should
probably say "search" not "google" but otherwise seems like a fine
solution.

(BTW, I notice that one of the earlier hits is Andrew's suggestion to
add the existing paragraph to our docs ;))

            regards, tom lane

On Feb 24, 2010, at 5:10 PM, Tom Lane wrote:

> Richard Huxton <dev@archonet.com> writes:
>> On 24/02/10 21:34, Tom Lane wrote:
>>> Hmm.  Jeff found some relevant material on perlref.  Should that
>>> link be
>>> added?  Should the link(s) be more specific than telling you to
>>> read the
>>> whole d*mn man page?  Neither of those pages are short, and each
>>> contains
>>> a wealth of material that isn't related to this issue.
>
>> Hmm - perhaps a suggestion to google for "perl nested named
>> subroutine".
>> That seems to give a set of relevant results. Includes perldiag,
>> perlref, our mailing lists and Apache's mod_perl (which makes sense).
>
> Seems like a reasonable idea to me --- any objections?  We should
> probably say "search" not "google" but otherwise seems like a fine
> solution.
>

Some sort of extended explanation would be helpful I think.
Admittedly, I didn't see the warning in the docs as I didn't look but
if I did see that I'd be very curious as to what exactly is
dangerous.  The reality is you cannot safely refer to variables
outside the scope of the sub.  Maybe just that sentence would suffice.
and a "for more details see the perlref documentation"

--
Jeff Trout <jeff@jefftrout.com>
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/