Pg7.4.x plpgsql trigger execution order bug

From: "Luki Rustianto" <lukirus(at)gmail(dot)com>
To: "General PostgreSQL List" <pgsql-general(at)postgresql(dot)org>
Subject: Pg7.4.x plpgsql trigger execution order bug
Date: 2007-06-28 04:51:02
Message-ID: a87d9f3a0706272151y240d536dpc92cbd4ab394404d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-06-28 05:01:59 Re: Column Default Clause and User Defined Functions
Previous Message Matthew O'Connor 2007-06-28 04:05:53 Re: autovacumm not working ?