Обсуждение: Bug #735: Server crash when using dynamic SQL and disabling recursion

Konstantin Katuev (katuev@dvbank.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

pgsql-bugs@postgresql.org writes:
> 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???

Changing the trigger set on a relation from within a trigger on that
same relation is not a good plan.  When you return from the trigger,
the trigger-calling code is now holding a dangling pointer to
no-longer-valid trigger data.  Instant core dump.

I'm not clear on why you feel you need to do that, but I'd suggest
finding another way.  Perhaps a rule instead of a trigger?  Or redesign
your table structure to make the problem go away in the first place?

            regards, tom lane