The following bug has been logged online:

Bug reference: 1831
Logged by: Greg Sabino Mullane
Email address: greg@turnstep.com
PostgreSQL version: 8.0.3
Operating system: Linux
Description: plperl gives error after reconnect.
Details:

Tested on 8.0.1 and in current cvs. This only happens if all the steps below
are followed, including the reconnect.


\c postgres

CREATE TABLE g (name TEXT);

CREATE OR REPLACE FUNCTION testone() RETURNS text LANGUAGE plperl AS
$$
spi_exec_query(qq{INSERT INTO g(name) VALUES ('abc')});
return "ok";
$$;

CREATE OR REPLACE FUNCTION enamer() RETURNS TRIGGER LANGUAGE plperl AS
$$
return;
$$;
CREATE TRIGGER trigtest BEFORE INSERT ON g FOR EACH ROW EXECUTE PROCEDURE
enamer();

\c postgres

select testone();


ERROR: error from Perl function: creation of Perl function failed:
(in cleanup) Undefined subroutine &main::mksafefunc called at (eval 4) line
2. at (eval 4) line 2.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"Greg Sabino Mullane" <greg@turnstep.com> writes:
> ERROR: error from Perl function: creation of Perl function failed:
> (in cleanup) Undefined subroutine &main::mksafefunc called at (eval 4) line
> 2. at (eval 4) line 2.

I could not duplicate this in either 8.0 or HEAD branches. It looks
a bit like an old bug that we had in plperl, though. Are you sure your
plperl.so is up to date?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Wed, Aug 17, 2005 at 06:46:20PM -0400, Tom Lane wrote:
> "Greg Sabino Mullane" <greg@turnstep.com> writes:
> > ERROR: error from Perl function: creation of Perl function failed:
> > (in cleanup) Undefined subroutine &main::mksafefunc called at (eval 4) line
> > 2. at (eval 4) line 2.
>
> I could not duplicate this in either 8.0 or HEAD branches. It looks
> a bit like an old bug that we had in plperl, though. Are you sure your
> plperl.so is up to date?

Could this be another "depends on the junk on your stack" bug? I
get different results depending on the OS and version of PostgreSQL:

8.0.3 (from CVS), FreeBSD 4.11-STABLE, Perl 5.8.7 (from ports)
* error with or without reconnect

HEAD, FreeBSD 4.11-STABLE, Perl 5.8.7 (from ports)
* success without reconnect, error with reconnect

8.0.3 (from CVS), Solaris 9, Perl 5.8.7 (from source)
* error with or without reconnect

HEAD, Solaris 9, Perl 5.8.7 (from source)
* error with or without reconnect

The configure options for all PostgreSQL builds are nearly identical
except that the builds on FreeBSD don't have --enable-cassert.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Wed, Aug 17, 2005 at 06:49:11PM -0600, Michael Fuhr wrote:
> On Wed, Aug 17, 2005 at 06:46:20PM -0400, Tom Lane wrote:
> > "Greg Sabino Mullane" <greg@turnstep.com> writes:
> > > ERROR: error from Perl function: creation of Perl function failed:
> > > (in cleanup) Undefined subroutine &main::mksafefunc called at (eval 4) line
> > > 2. at (eval 4) line 2.
> >
> > I could not duplicate this in either 8.0 or HEAD branches. It looks
> > a bit like an old bug that we had in plperl, though. Are you sure your
> > plperl.so is up to date?
>
> Could this be another "depends on the junk on your stack" bug? I
> get different results depending on the OS and version of PostgreSQL:

Also, on my systems the trigger isn't necessary, but the function
call history is significant. This is in HEAD:

\c test

CREATE OR REPLACE FUNCTION foo() RETURNS text AS $$
return "foo";
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION bar() RETURNS text AS $$
my $rv = spi_exec_query("SELECT foo() AS x");
return $rv->{rows}[0]->{x};
$$ LANGUAGE plperl;

\c test

SELECT bar();
ERROR: error from Perl function: creation of Perl function failed: (in cleanup) Undefined subroutine &main::mksafefunc called at (eval 5) line 2. at (eval 5) line 2.

SELECT bar();
ERROR: error from Perl function: creation of Perl function failed: (in cleanup) Undefined subroutine &main::mksafefunc called at (eval 5) line 2. at (eval 5) line 2.

SELECT foo();
foo
-----
foo
(1 row)

SELECT bar();
bar
-----
foo
(1 row)

I verified that the postmaster is using a current plperl.so by
adding a debugging ereport() statement in plperl_call_perl_func()
(output not shown above).

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Michael Fuhr <mike@fuhr.org> writes:
> Could this be another "depends on the junk on your stack" bug?

Looks that way --- but I've still had no success in reproducing it,
either on x86/Linux or PPC/Darwin. Anyone have some variant test
cases?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Thu, Aug 18, 2005 at 12:26:28AM -0400, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > Could this be another "depends on the junk on your stack" bug?
>
> Looks that way --- but I've still had no success in reproducing it,
> either on x86/Linux or PPC/Darwin. Anyone have some variant test
> cases?

I see different results depending on whether the calling function
is plperl or plperlu. Here again are the functions -- I'll change
only the language:

CREATE OR REPLACE FUNCTION foo() RETURNS text AS $$
return "foo";
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION bar() RETURNS text AS $$
my $rv = spi_exec_query("SELECT foo()");
return $rv->{rows}[0]->{foo};
$$ LANGUAGE plperl;

SELECT bar();

With HEAD on Solaris 9/sparc I don't have to reconnect before the
SELECT to get the error (I tested both ways, with and without a
reconnect, and it made no difference). Here's what I get with
various language combinations:

foo plperl, bar plperl - Undefined subroutine &main::mksafefunc
foo plperl, bar plperlu - ok
foo plperlu, bar plperl - Undefined subroutine &main::mkunsafefunc
foo plperlu, bar plperlu - ok

I get the same results on FreeBSD 4.11-STABLE/x86 but I have to
reconnect before the SELECT to get the error. On both systems,
if I execute SELECT foo() before SELECT bar() then I don't get
the error.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Tom Lane asked:
> I could not duplicate this in either 8.0 or HEAD branches. It looks
> a bit like an old bug that we had in plperl, though. Are you sure your
> plperl.so is up to date?

Looks like Michael is already far along, but yes, my plperl.so was up to date.
This is on a Red Hat Linux box, using --with-perl and --with-gnu-ld as the
only compile options. It's a very subtle bug: on my box, simply leaving out
the trigger definition, or having the function not do a spi_exec_query will
not raise the error. I've worked around this locally by not using plperlu
(hence the original reason to switch to another user), but I sure miss
being able to do "use strict"

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200508181050
https://www.biglumber.com/x/web?pk=2...9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEARECAAYFAkMEoFkACgkQvJuQZxSWSsjTpwCgmt9kLApba6 xDygvgl5qb/vdc
Zh4AoPx1or9LLWSTUZQDcDjxJCfNBb08
=5Jt7
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Thu, Aug 18, 2005 at 02:52:02PM -0000, Greg Sabino Mullane wrote:
> Tom Lane asked:
> > I could not duplicate this in either 8.0 or HEAD branches. It looks
> > a bit like an old bug that we had in plperl, though. Are you sure your
> > plperl.so is up to date?
>
> Looks like Michael is already far along, but yes, my plperl.so was up to date.
> This is on a Red Hat Linux box, using --with-perl and --with-gnu-ld as the
> only compile options. It's a very subtle bug: on my box, simply leaving out
> the trigger definition, or having the function not do a spi_exec_query will
> not raise the error.

Tom Lane once mentioned that "Valgrind is fairly useless for debugging
postgres," but has anybody tried it for this problem? I tried using
the FreeBSD port but it's having trouble (first I had to hack in
support for a system call, now it's terminating the postmaster with
SIBGUS on a call to setproctitle).

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Tom Lane once mentioned that "Valgrind is fairly useless for debugging
> postgres," but has anybody tried it for this problem? I tried using
> the FreeBSD port but it's having trouble (first I had to hack in
> support for a system call, now it's terminating the postmaster with
> SIBGUS on a call to setproctitle).

I've got valgrind working, but not sure exactly how to use it to debug
this problem. What's the procedure?

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200508190955
https://www.biglumber.com/x/web?pk=2...9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iEYEARECAAYFAkMF5N8ACgkQvJuQZxSWSsi6eQCggFJT5i9phq GomACJk/ZIKDgS
vv8AnROppubywG9bY2ZU26MMfG3lKPdj
=+srT
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Fri, Aug 19, 2005 at 01:56:39PM -0000, Greg Sabino Mullane wrote:
> > Tom Lane once mentioned that "Valgrind is fairly useless for debugging
> > postgres," but has anybody tried it for this problem?
>
> I've got valgrind working, but not sure exactly how to use it to debug
> this problem. What's the procedure?

I haven't used valgrind much, but I was thinking of initdb'ing a
test cluster, loading the PL/Perl functions into it, and running
valgrind on a single-user-mode postgres process in which you issue
a query that causes the problem. I'm wondering if valgrind's memory
checks will show the code accessing memory that hasn't been initialized.

--
Michael Fuhr

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