BUG #6511: calling spi_exec_query from non-main package, results in: couldn't fetch $_TD

The following bug has been logged on the website:

Bug reference: 6511
Logged by: Mark Murawski
Email address: marko(at)kobaz(dot)net
PostgreSQL version: 9.1.3
Operating system: Linux - Debian Squeeze postgres 9.1 from backports
Description:

create table mytable ( id integer );

--
CREATE OR REPLACE FUNCTION mytrigger() RETURNS trigger AS $BODY$
return undef
$BODY$ LANGUAGE plperlu VOLATILE COST 100;

--
CREATE TRIGGER "insert" BEFORE INSERT ON mytable FOR EACH ROW EXECUTE
PROCEDURE mytrigger();

--
CREATE OR REPLACE FUNCTION myfunc() RETURNS text AS $BODY$
package foo;
sub foo {
main::spi_exec_query(q{INSERT INTO mytable VALUES (1) RETURNING id});
};

package main;
foo::foo();
return;
$BODY$ LANGUAGE plperlu VOLATILE COST 100;

--

pbx=# select * from myfunc();
ERROR: couldn't fetch $_TD at line 4.
CONTEXT: PL/Perl function "myfunc"

-- this works:

CREATE OR REPLACE FUNCTION myfunc() RETURNS text AS $BODY$
package foo;
sub foo {
package main;
spi_exec_query(q{INSERT INTO mytable VALUES (1) RETURNING id});
};

package main;
foo::foo();
return;
$BODY$ LANGUAGE plperlu VOLATILE COST 100;

select * from myfunc();
myfunc
--------

(1 row)

drop table mytable;
drop function mytrigger();
drop function myfunc();

On Sat, Mar 3, 2012 at 12:19, <marko(at)kobaz(dot)net> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      6511
> Logged by:          Mark Murawski
> Email address:      marko(at)kobaz(dot)net
> PostgreSQL version: 9.1.3
> Operating system:   Linux - Debian Squeeze postgres 9.1 from backports
> Description:
>
> CREATE OR REPLACE FUNCTION myfunc()  RETURNS text AS $BODY$
> package foo;
> sub foo {
>  main::spi_exec_query(q{INSERT INTO mytable VALUES (1) RETURNING id});
> };
>
> package main;
> foo::foo();
> return;
> $BODY$  LANGUAGE plperlu VOLATILE  COST 100;
>
> --
>
> pbx=# select * from myfunc();
> ERROR:  couldn't fetch $_TD at line 4.
> CONTEXT:  PL/Perl function "myfunc"

[ Calling a plperl trigger function from a plperl function ]

Yeah, there were some optimization done for 9.1 to try and make calls
a bit faster. The problem is we are fetching "_TD" not "main::_TD",
which means we try to find and use $_TD from whatever the current
package is. This should only happen from a nested plperl to plperl
trigger where the outer call was in a different package, otherwise the
package is always main.

The attached fixes it for me, It would be great if you could confirm that.

Thanks for the report!

On Tue, Mar 06, 2012 at 09:08:25PM -0700, Alex Hunsaker wrote:
> [ Calling a plperl trigger function from a plperl function ]
>
> Yeah, there were some optimization done for 9.1 to try and make calls
> a bit faster. The problem is we are fetching "_TD" not "main::_TD",
> which means we try to find and use $_TD from whatever the current
> package is. This should only happen from a nested plperl to plperl
> trigger where the outer call was in a different package, otherwise the
> package is always main.
>
> The attached fixes it for me, It would be great if you could confirm that.
>
> Thanks for the report!

So, should this be applied?

---------------------------------------------------------------------------

> *** a/src/pl/plperl/plperl.c
> --- b/src/pl/plperl/plperl.c
> ***************
> *** 2062,2068 **** plperl_call_perl_trigger_func(plperl_proc_desc *desc, FunctionCallInfo fcinfo,
> ENTER;
> SAVETMPS;
>
> ! TDsv = get_sv("_TD", 0);
> if (!TDsv)
> elog(ERROR, "couldn't fetch $_TD");
>
> --- 2062,2068 ----
> ENTER;
> SAVETMPS;
>
> ! TDsv = get_sv("main::_TD", 0);
> if (!TDsv)
> elog(ERROR, "couldn't fetch $_TD");
>

>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Thu, Mar 15, 2012 at 16:13, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Tue, Mar 06, 2012 at 09:08:25PM -0700, Alex Hunsaker wrote:
>> [ Calling a plperl trigger function from a plperl function ]
>>
>> Yeah, there were some optimization done for 9.1 to try and make calls
>> a bit faster. The problem is we are fetching "_TD" not "main::_TD",
>> which means we try to find and use $_TD from whatever the current
>> package is. This should only happen from a nested plperl to plperl
>> trigger where the outer call was in a different package, otherwise the
>> package is always main.
>>
>> The attached fixes it for me, It would be great if you could confirm that.
>>
>> Thanks for the report!
>
> So, should this be applied?

Yes, but I would like to get conformation that it fixes the issue.
Either way Ill probably just end up submitting it to the next commit
fest. (I know most commiters are still busy with the current one).

On 03/16/12 13:48, Alex Hunsaker wrote:
> On Thu, Mar 15, 2012 at 16:13, Bruce Momjian<bruce(at)momjian(dot)us> wrote:
>> On Tue, Mar 06, 2012 at 09:08:25PM -0700, Alex Hunsaker wrote:
>>> [ Calling a plperl trigger function from a plperl function ]
>>>
>>> Yeah, there were some optimization done for 9.1 to try and make calls
>>> a bit faster. The problem is we are fetching "_TD" not "main::_TD",
>>> which means we try to find and use $_TD from whatever the current
>>> package is. This should only happen from a nested plperl to plperl
>>> trigger where the outer call was in a different package, otherwise the
>>> package is always main.
>>>
>>> The attached fixes it for me, It would be great if you could confirm that.
>>>
>>> Thanks for the report!
>> So, should this be applied?
> Yes, but I would like to get conformation that it fixes the issue.
> Either way Ill probably just end up submitting it to the next commit
> fest. (I know most commiters are still busy with the current one).

Fixes the problem. Thanks. I always like one line fixes.

Excerpts from Alex Hunsaker's message of mié mar 07 01:08:25 -0300 2012:
> On Sat, Mar 3, 2012 at 12:19, <marko(at)kobaz(dot)net> wrote:

> Yeah, there were some optimization done for 9.1 to try and make calls
> a bit faster. The problem is we are fetching "_TD" not "main::_TD",
> which means we try to find and use $_TD from whatever the current
> package is. This should only happen from a nested plperl to plperl
> trigger where the outer call was in a different package, otherwise the
> package is always main.
>
> The attached fixes it for me, It would be great if you could confirm that.

Thanks -- applied to 9.1 and master.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Mar 19, 2012 at 14:40, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
>
> Excerpts from Alex Hunsaker's message of mié mar 07 01:08:25 -0300 2012:

>> The attached fixes it for me, It would be great if you could confirm that.
>
> Thanks -- applied to 9.1 and master.

Thanks!