The following bug has been logged online:

Bug reference: 1391
Logged by: Sokolov Yura
Email address: falcon@intercable.ru
PostgreSQL version: 8.0.0 rc2
Operating system: Windows2003
Description: Perl trusted language triggers can't properly access
$_SHARED
Details:

I created triggers In Perl trusted (ActivePerl 5.8.4 build 810) and it
crashes when triggers FOR EACH STATEMENT tried to access $_SHARED when
triggers FOR EACH ROW not accessed it before

sample:

-- I run it with command line
-- psql -f "pgbug.sql" template1 postgres
CREATE DATABASE intcab
WITH OWNER = postgres
ENCODING = 'WIN'
TABLESPACE = pg_default;
----------------------------------------------------
\c intcab postgres
-------------------------------------------------------
CREATE TABLE voiceshort
(
code int4 NOT NULL,
date date NOT NULL,
portfrom char(6) NOT NULL,
startcall time(0) NOT NULL,
ru numeric(9,4) NOT NULL,
CONSTRAINT "PK__VoiceShort" PRIMARY KEY (date, startcall, portfrom)
)
WITHOUT OIDS;
ALTER TABLE voiceshort OWNER TO postgres;
-------------------------------------------------------
CREATE TABLE coderusum
(
code int4 NOT NULL,
ru numeric(9,4) NOT NULL,
CONSTRAINT "PK__coderusum" PRIMARY KEY (code)
)
WITHOUT OIDS;
ALTER TABLE coderusum OWNER TO postgres;
-------------------------------------------------------
CREATE OR REPLACE FUNCTION voiceshort_addru_perl()
RETURNS "trigger" AS
$BODY$
if(!defined $_SHARED{VShAddru}){$_SHARED{VShAddru}={};}
$_SHARED{VShAddru}->{$_TD->{new}{code}}+=$_TD->{new}{ru};
return;
$BODY$
LANGUAGE 'plperl' VOLATILE;
ALTER FUNCTION voiceshort_addru_perl() OWNER TO postgres;
------------------------------------------------------
CREATE OR REPLACE FUNCTION voiceshort_delru_perl()
RETURNS "trigger" AS
$BODY$
if(!defined $_SHARED{VShAddru}){$_SHARED{VShAddru}={};}
$_SHARED{VShAddru}->{$_TD->{old}{code}}-=$_TD->{old}{ru};
return;
$BODY$
LANGUAGE 'plperl' VOLATILE;
ALTER FUNCTION voiceshort_delru_perl() OWNER TO postgres;
------------------------------------------------------
CREATE OR REPLACE FUNCTION voiceshort_addru_finnaly_perl()
RETURNS "trigger" AS
$BODY$
# All variants below makes same error
if(exists $_SHARED{VShAddru}){
# if(defined $_SHARED{VShAddru}){
# if(defined(%_SHARED) && defined($_SHARED{VShAddru})){
while(my ($code,$ru)=each %{$_SHARED{VShAddru}}){
spi_exec_query("update coderusum set ru=ru+($ru) where code=$code");
}
delete $_SHARED{VShAddru};
}
return;
$BODY$
LANGUAGE 'plperl' VOLATILE;
ALTER FUNCTION voiceshort_addru_finnaly_perl() OWNER TO postgres;
------------------------------------------------------
CREATE TRIGGER e_tr_voiceshort_insert
AFTER INSERT
ON voiceshort
FOR EACH ROW
EXECUTE PROCEDURE voiceshort_addru_perl();
----------------------------------------------------
CREATE TRIGGER e_tr_voiceshort_delete
AFTER DELETE
ON voiceshort
FOR EACH ROW
EXECUTE PROCEDURE voiceshort_delru_perl();
----------------------------------------------------
CREATE TRIGGER d_tr_voiceshort_afterall
AFTER INSERT OR UPDATE OR DELETE
ON voiceshort
FOR EACH STATEMENT
EXECUTE PROCEDURE voiceshort_addru_finnaly_perl();
----------------------------------------------------
begin;
insert into coderusum values(1,0);
insert into coderusum values(2,0);
insert into coderusum values(3,0);
commit;
----------------------------------------------------
-- All Inserts below works fine
begin;
insert into voiceshort values (1,'2004-01-01','e1_1','00:00:01',1.1);
insert into voiceshort values (2,'2004-01-01','e2_1','00:00:04',1.6);
insert into voiceshort values (3,'2004-01-01','e1_3','00:00:05',2.1);
insert into voiceshort values (2,'2004-01-01','e4_7','00:00:01',3.1);
insert into voiceshort values (1,'2004-01-01','e2_1','00:00:21',4.4);
insert into voiceshort values (3,'2004-01-01','e1_5','00:00:31',4.1);
insert into voiceshort values (2,'2004-01-01','e1_6','00:00:06',2.3);
commit;
----------------------------------------------------
-- Checks coderusum
select * from coderusum;
----------------------------------------------------
-- First case
-- Delete works well when there are some data
delete from voiceshort;
----------------------------------------------------
-- Checks coderusum
select * from coderusum;
----------------------------------------------------
-- Second case
-- Delete crashes when there isn't any data
delete from voiceshort;
----------------------------------------------------
-- Checks coderusum
select * from coderusum;
----------------------------------------------------
-- The same thing happens when I create trigger
-- BEFORE FOR EACH STATEMENT which tryed to access %_SHARED

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

"Sokolov Yura" <falcon@intercable.ru> writes:
> I created triggers In Perl trusted (ActivePerl 5.8.4 build 810) and it
> crashes when triggers FOR EACH STATEMENT tried to access $_SHARED when
> triggers FOR EACH ROW not accessed it before

Actually _SHARED had nothing to do with it: the trigger handler was
trying to fetch new/old tuples even though FOR EACH STATEMENT.
I've applied the attached patch. Thanks for the report!

regards, tom lane

*** src/pl/plperl/plperl.c.orig Tue Jan 11 10:35:22 2005
--- src/pl/plperl/plperl.c Fri Jan 14 11:19:23 2005
***************
*** 338,372 ****
if (TRIGGER_FIRED_BY_INSERT(tdata->tg_event))
{
event = "INSERT";
! hv_store(hv, "new", 3,
! plperl_hash_from_tuple(tdata->tg_trigtuple, tupdesc),
! 0);
}
else if (TRIGGER_FIRED_BY_DELETE(tdata->tg_event))
{
event = "DELETE";
! hv_store(hv, "old", 3,
! plperl_hash_from_tuple(tdata->tg_trigtuple, tupdesc),
! 0);
}
else if (TRIGGER_FIRED_BY_UPDATE(tdata->tg_event))
{
event = "UPDATE";
! hv_store(hv, "old", 3,
! plperl_hash_from_tuple(tdata->tg_trigtuple, tupdesc),
! 0);
! hv_store(hv, "new", 3,
! plperl_hash_from_tuple(tdata->tg_newtuple, tupdesc),
! 0);
}
! else {
event = "UNKNOWN";
- }

hv_store(hv, "event", 5, newSVpv(event, 0), 0);
hv_store(hv, "argc", 4, newSViv(tdata->tg_trigger->tgnargs), 0);

! if (tdata->tg_trigger->tgnargs != 0)
{
AV *av = newAV();
for (i=0; i < tdata->tg_trigger->tgnargs; i++)
--- 338,376 ----
if (TRIGGER_FIRED_BY_INSERT(tdata->tg_event))
{
event = "INSERT";
! if (TRIGGER_FIRED_FOR_ROW(tdata->tg_event))
! hv_store(hv, "new", 3,
! plperl_hash_from_tuple(tdata->tg_trigtuple, tupdesc),
! 0);
}
else if (TRIGGER_FIRED_BY_DELETE(tdata->tg_event))
{
event = "DELETE";
! if (TRIGGER_FIRED_FOR_ROW(tdata->tg_event))
! hv_store(hv, "old", 3,
! plperl_hash_from_tuple(tdata->tg_trigtuple, tupdesc),
! 0);
}
else if (TRIGGER_FIRED_BY_UPDATE(tdata->tg_event))
{
event = "UPDATE";
! if (TRIGGER_FIRED_FOR_ROW(tdata->tg_event))
! {
! hv_store(hv, "old", 3,
! plperl_hash_from_tuple(tdata->tg_trigtuple, tupdesc),
! 0);
! hv_store(hv, "new", 3,
! plperl_hash_from_tuple(tdata->tg_newtuple, tupdesc),
! 0);
! }
}
! else
event = "UNKNOWN";

hv_store(hv, "event", 5, newSVpv(event, 0), 0);
hv_store(hv, "argc", 4, newSViv(tdata->tg_trigger->tgnargs), 0);

! if (tdata->tg_trigger->tgnargs > 0)
{
AV *av = newAV();
for (i=0; i < tdata->tg_trigger->tgnargs; i++)

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

http://www.postgresql.org/docs/faqs/FAQ.html