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(at)jefftrout(dot)com>
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/