I've found another plperl bug in the most recent CVS sources (see
end of message for file versions). Sorry if this is a known issue
but I don't remember seeing it.

If spi_exec_query() fails in a plperl function, subsequent calls
to plperl or plperlu functions that hadn't been previously loaded
will fail with "Undefined subroutine &main::mksafefunc". Functions
that had already been loaded will continue to work, at least in
simple cases. I don't know what conditions besides spi_exec_query()
failure will cause this problem, but syntax errors in the Perl code
itself don't appear to cause it.

In the examples below, if test2() is plperl then the subsequent
call to test3() will fail whether test3() is plperl or plperlu.
However, if test2() is plperlu then test3() will work either way.

The order of the CREATE FUNCTION statements is irrelevant; the call
order determines the failures.


\set VERBOSITY verbose

CREATE OR REPLACE FUNCTION test1() RETURNS INTEGER AS $$
return 1;
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION test2() RETURNS INTEGER AS $$
spi_exec_query("SELECT FOOBAR"); # intentional error
return 2;
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION test3() RETURNS INTEGER AS $$
return 3;
$$ LANGUAGE plperl;

SELECT test1();
test1
-------
1
(1 row)

SELECT test2();
ERROR: 42703: column "foobar" does not exist
CONTEXT: SQL statement "SELECT FOOBAR"
LOCATION: transformColumnRef, parse_expr.c:1085

SELECT test3();
ERROR: XX000: creation of function failed: (in cleanup) Undefined subroutine &main::mksafefunc called at (eval 6) line 2.
LOCATION: plperl_create_sub, plperl.c:709

SELECT test1();
test1
-------
1
(1 row)


File versions:

/ppport.h/1.8/Mon Aug 30 02:54:41 2004//
/spi_internal.c/1.5/Mon Sep 13 20:08:59 2004//
/spi_internal.h/1.4/Mon Sep 13 20:08:59 2004//
/GNUmakefile/1.18/Sat Nov 20 20:53:13 2004//
/SPI.xs/1.10/Sat Nov 20 20:53:13 2004//
/plperl.c/1.59/Sat Nov 20 20:53:13 2004//

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
Yeah ... there are other ways to cause this but the short of it is
that plperl's error handling is entirely broken. See nearby rant
in pghackers.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

On Sun, Nov 21, 2004 at 03:16:36AM -0500, Tom Lane wrote:
Oops...I did see that thread. I must have prematurely free()'d the
memory of it.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings