Обсуждение: Pg7.4.x plpgsql trigger execution order bug

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

"Luki Rustianto" <lukirus@gmail.com> writes:
> 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 quote the 8.0 release notes:

: Observe the following incompatibilities:
: ...
: Nondeferred AFTER triggers are now fired immediately after completion of
: the triggering query, rather than upon finishing the current interactive
: command. This makes a difference when the triggering query occurred
: within a function: the trigger is invoked before the function proceeds
: to its next operation.

This change is not going to be back-patched into 7.x, because it would
break applications that depended on the old behavior.

            regards, tom lane