The following bug has been logged online:

Bug reference: 1950
Logged by: Greg Sabino Mullane
Email address: greg (AT) turnstep (DOT) com
PostgreSQL version: 8.0 and cvs
Operating system: Linux
Description: Subroutine info cached in pl/perl
Details:

Inner subroutines seem to be caching initial values (e.g. the all important
%_TD hash)


\o /dev/null
CREATE TEMP TABLE event_problem (a int);
CREATE OR REPLACE FUNCTION event_problem() RETURNS TRIGGER LANGUAGE plperlu
AS
$$

my $event = $_TD->{event};
elog(INFO, "Top event : $event");
my $newname = $_TD->{new}{a};
elog(INFO, "Top newname : $newname");
&subber($event);

sub subber {
my $arg = shift;
elog(INFO, join " | " => caller(0));
elog(INFO, join " | " => caller(1));
elog(INFO, "Sub info : $info");
elog(INFO, "Sub global : $event");
elog(INFO, "Sub direct : $_TD->{event}");
my $newname = $_TD->{new}{a};
elog(INFO, "Sub newname : $newname");
}

elog(INFO, "Bottom event : $event");
return;
$$;

CREATE TRIGGER event_problem BEFORE INSERT ON event_problem
FOR EACH ROW EXECUTE PROCEDURE event_problem();

CREATE TRIGGER event_problem2 BEFORE UPDATE ON event_problem
FOR EACH ROW EXECUTE PROCEDURE event_problem();

-- Also happens with a single BEFORE UPDATE OR INSERT

\o
INSERT INTO event_problem(a) VALUES (22);
UPDATE event_problem SET a = 33;
INSERT INTO event_problem(a) VALUES (44);
UPDATE event_problem SET a = 55;


Outputs:

INFO: Top event : INSERT
INFO: Top newname : 22
INFO: main | (eval 1) | 8 | main::subber | 1 | | | | 0 |
INFO: main | -e | 0 | main::__ANON__ | 1 | 0 | | | 0 |
INFO: Sub info :
INFO: Sub global : INSERT
INFO: Sub direct : INSERT
INFO: Sub newname : 22
INFO: Bottom event : INSERT
INSERT 0 1
INFO: Top event : UPDATE
INFO: Top newname : 33
INFO: main | (eval 1) | 8 | main::subber | 1 | | | | 0 |
INFO: main | -e | 0 | main::__ANON__ | 1 | 0 | | | 0 |
INFO: Sub info :
INFO: Sub global : INSERT
INFO: Sub direct : INSERT
INFO: Sub newname : 22
INFO: Bottom event : UPDATE
UPDATE 1
INFO: Top event : INSERT
INFO: Top newname : 44
INFO: main | (eval 1) | 8 | main::subber | 1 | | | | 0 |
INFO: main | -e | 0 | main::__ANON__ | 1 | 0 | | | 0 |
INFO: Sub info :
INFO: Sub global : INSERT
INFO: Sub direct : INSERT
INFO: Sub newname : 22
INFO: Bottom event : INSERT
INSERT 0 1
INFO: Top event : UPDATE
INFO: Top newname : 55
INFO: main | (eval 1) | 8 | main::subber | 1 | | | | 0 |
INFO: main | -e | 0 | main::__ANON__ | 1 | 0 | | | 0 |
INFO: Sub info :
INFO: Sub global : INSERT
INFO: Sub direct : INSERT
INFO: Sub newname : 22
INFO: Bottom event : UPDATE
INFO: Top event : UPDATE
INFO: Top newname : 55
INFO: main | (eval 1) | 8 | main::subber | 1 | | | | 0 |
INFO: main | -e | 0 | main::__ANON__ | 1 | 0 | | | 0 |
INFO: Sub info :
INFO: Sub global : INSERT
INFO: Sub direct : INSERT
INFO: Sub newname : 22
INFO: Bottom event : UPDATE
UPDATE 2

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

http://archives.postgresql.org