Bug #735: Server crash when using dynamic SQL and disabling recursion

Konstantin Katuev (katuev(at)dvbank(dot)ru) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Server crash when using dynamic SQL and disabling recursion

Long Description
When trying to develop trigger on plpgsql, that will update the own relation,
i met server (backend) crash due to signal 11.
Have tryed this both on RedHat 7.0 with gcc 2.96 and
Solaris 8 on sparc with gcc 3.1
PostgreSQL 7.2.1 was compiled myself from sources.
Following is the example of sql script that crashes server if you
remove marked comments (this is not real code, just test one).
May be i am doing something wrong???

Sincerely,
Konstantin Katuev,
Vladivostok, RUSSIA

Sample Code
--CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS '/usr/local/pgsql/lib/plpgsql.so', 'plpgsql_call_handler' LANGUAGE 'C';
--CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
--HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';
DROP TABLE test1;
DROP TABLE test2;
CREATE TABLE test1 ( cnam char(3), nm1 char(1), nm2 char(2) );
INSERT INTO test1 VALUES('NM1','+','-');
INSERT INTO test1 VALUES('NM2','-','+');
CREATE TABLE test2 ( numb int PRIMARY KEY, cname char(3), cval char(1) );

DROP VIEW test3;
CREATE VIEW test3 AS SELECT * FROM test2;

CREATE OR REPLACE FUNCTION trgfun() RETURNS opaque AS '
DECLARE
curs1 refcursor;
rec1 record;
rec2 record;
rpgClass record;

BEGIN
NEW.cval=''*'';

RAISE NOTICE ''Trigger called'';

-- disable triggers
-- DROP TRIGGER trg1 ON test2;
-- another way
SELECT INTO rpgClass * FROM pg_class WHERE relname=''test2'';
UPDATE pg_class SET reltriggers = 0 WHERE relname=''test2'';
RAISE NOTICE ''Reltriggers=%'',rpgClass.reltriggers;

-- Find previous value:
-- if here will be FROM test2 trigger will crash server immediately.
SELECT INTO rec1 * FROM test3 WHERE numb<NEW.numb;

IF FOUND THEN
-- dynamic statement will crash server
-- OPEN curs1 FOR EXECUTE ''select ''
-- || NEW.cname
-- || '' AS snm FROM test1 WHERE cnam=''''''
-- || rec1.cname || '''''''';
-- static should work fine
OPEN curs1 FOR SELECT nm1 AS snm FROM test1 WHERE cnam=''NM2'';
FETCH curs1 INTO rec2;
IF FOUND THEN
NEW.cval := rec2.snm;
END IF;
CLOSE curs1;
END IF;

/* Remove comment to crash server....
-- Find NEXT value:
SELECT INTO rec1 * FROM test2 WHERE numb>NEW.numb;
IF FOUND THEN
OPEN curs1 FOR EXECUTE ''select ''
|| rec1.cname
|| '' AS snm FROM test1 WHERE cnam=''''''
|| NEW.cname || '''''''';
FETCH curs1 INTO rec2;
IF FOUND THEN
// I need to disable triggers in order to prevent them
// to work for this statement
UPDATE test2 SET cval=rec2.snm WHERE numb=rec1.numb;
END IF;
CLOSE curs1;
END IF;
*/
-- enable triggers

UPDATE pg_class SET reltriggers = (select count(*) from pg_trigger
where pg_class.oid = tgrelid)
WHERE relname=''test2'';

-- I know it is exactly 1
-- UPDATE pg_class SET reltriggers = rpgClass.reltriggers WHERE relname=''test2'';

-- another way: same results
--CREATE TRIGGER trg1 BEFORE UPDATE ON test2
--FOR EACH ROW EXECUTE PROCEDURE trgfun();

RETURN NEW;

END;
' LANGUAGE 'plpgsql';

INSERT INTO test2(numb,cname) VALUES(1,'NM1');
INSERT INTO test2(numb,cname) VALUES(2,'NM1');
INSERT INTO test2(numb,cname) VALUES(3,'NM2');
INSERT INTO test2(numb,cname) VALUES(4,'NM2');
INSERT INTO test2(numb,cname) VALUES(5,'NM1');

CREATE TRIGGER trg1 BEFORE UPDATE ON test2
FOR EACH ROW EXECUTE PROCEDURE trgfun();

UPDATE test2 SET cname='NM1' WHERE numb=3;
UPDATE test2 SET cname='NM2' WHERE numb=3;

No file was uploaded with this report