Skip site navigation (1) Skip section navigation (2)

BUG #1391: Perl trusted language triggers can't properly access $_SHARED

From: "Sokolov Yura" <falcon(at)intercable(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1391: Perl trusted language triggers can't properly access $_SHARED
Date: 2005-01-12 14:47:51
Message-ID: 200501121447.j0CElpUV004643@developer.pgadmin.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      1391
Logged by:          Sokolov Yura
Email address:      falcon(at)intercable(dot)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

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-01-12 15:37:14
Subject: Re: 8rc5 on OpenBSD
Previous:From: Richard HuxtonDate: 2005-01-12 13:55:17
Subject: Re: RULES doesn't work as expected

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group