Обсуждение: BUG #1391: Perl trusted language triggers can't properly access $_SHARED
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
"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++)