Search Postgresql Archives

Curious plperl behavior

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx>
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/




--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux