Pg7.4.x plpgsql trigger execution order bug - Mailing list pgsql-general

Hi All,

I've found on pg7.4.x that plpgsql trigger will not wait for finishing
one line of command before executing to the next line, specially if
that command is firing another trigger on another table. This is very
wrong on my point of view. This only happen on pg7. To check:
(create master and detail table, trigger in master table is firing
trigger on detail table)


CREATE OR REPLACE FUNCTION "public"."master_tr_func" () RETURNS trigger AS'
BEGIN
  RAISE WARNING ''First in Master'';
  INSERT INTO detail values (NEW.mid + 1);
  RAISE WARNING ''Last in Master'';
  RETURN NEW;
END;
'LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION "public"."detail_tr_func" () RETURNS trigger AS'
DECLARE
       testah numeric;
BEGIN

  RAISE warning ''BEGIN: INSIDE detail'';
  RAISE warning ''END: INSIDE detail'';
  RETURN NEW;
END;
'LANGUAGE 'plpgsql';

CREATE TABLE "public"."master" (
  "mid" INTEGER
) WITH OIDS;

CREATE TRIGGER "master_tr" AFTER INSERT OR UPDATE
ON "public"."master" FOR EACH ROW
EXECUTE PROCEDURE "public"."master_tr_func"();


CREATE TABLE "public"."detail" (
  "did" INTEGER
) WITH OIDS;

CREATE TRIGGER "detail_tr" AFTER INSERT OR UPDATE
ON "public"."detail" FOR EACH ROW
EXECUTE PROCEDURE "public"."detail_tr_func"();


now we test:

lukitest=# select version();
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 7.4.17 on i486-pc-linux-gnu, compiled by GCC cc (GCC)
4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
(1 row)

lukitest=# insert into master values (1);
WARNING:  First in Master
WARNING:  Last in Master
WARNING:  BEGIN: INSIDE detail
WARNING:  END: INSIDE detail
INSERT 410367 1


See? it should be (on pg8):


luki=# select version();
                                          version
-------------------------------------------------------------------------------------------
 PostgreSQL 8.1.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.3.4 (Debian 1:3.3.4-3)
(1 row)
luki=# insert into master values (1);
WARNING:  First in Master
WARNING:  BEGIN: INSIDE detail
CONTEXT:  SQL statement "INSERT INTO detail values ( $1  + 1)"
PL/pgSQL function "master_tr_func" line 3 at SQL statement
WARNING:  END: INSIDE detail
CONTEXT:  SQL statement "INSERT INTO detail values ( $1  + 1)"
PL/pgSQL function "master_tr_func" line 3 at SQL statement
WARNING:  Last in Master
INSERT 426015 1