Re: Trigger firing order

Hello all,

I have a problem with PostgreSQL. Here is the description of the problem:

First, I create tables and triggers for them, so that after insert to one
table a row is inserted to another table.

CREATE TABLE t_foo (
foo_id SERIAL PRIMARY KEY,
foo_value NUMERIC NOT NULL
);

CREATE TABLE t_bar (
bar_foo INT4 NOT NULL REFERENCES t_foo (foo_id),
bar_state INT2 NOT NULL
);

CREATE FUNCTION fn_foo_ains()
RETURNS OPAQUE AS '
BEGIN
RAISE NOTICE ''fn_foo_ains: Start'';
INSERT
INTO t_bar (bar_foo, bar_state)
VALUES (NEW.foo_id, 1);

/* There will be code that examines table */
/* modified by fn_bar_ains() (see below) */

RAISE NOTICE ''fn_foo_ains: End'';
RETURN NULL;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER trg_foo_ains
AFTER INSERT ON t_foo
FOR EACH ROW
EXECUTE PROCEDURE fn_foo_ains();

CREATE FUNCTION fn_bar_ains()
RETURNS OPAQUE AS '
BEGIN
RAISE NOTICE ''fn_bar_ains: Start'';

/* There will be code that modifies the third table */

RAISE NOTICE ''fn_bar_ains: End'';
RETURN NULL;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER trg_bar_ains
AFTER INSERT
ON t_bar
FOR EACH ROW
EXECUTE PROCEDURE fn_bar_ains();

Then I insert a row. Here is output I get:

peroon=# INSERT INTO t_foo (foo_value) VALUES (2000);
NOTICE: fn_foo_ains: Start
NOTICE: fn_foo_ains: End
NOTICE: fn_bar_ains: Start
NOTICE: fn_bar_ains: End
INSERT 121365 1

So, when I do an "INSERT INTO t_bar" inside the "TRIGGER AFTER INSERT ON
t_foo", the trigger procedure on t_bar is executed only when the trigger
procedure on t_foo returns.

The behaviour I expect would be something like that:

NOTICE: fn_foo_ains: Start
NOTICE: fn_bar_ains: Start
NOTICE: fn_bar_ains: End
NOTICE: fn_foo_ains: End

, so that all changes made by fn_bar_ains() would be visible to
fn_foo_ains() after the "INSERT INTO t_bar" completes.

Is there any way to make the system work this way?

Thanks in advance,

Alex Bolenok.

"Alex Bolenok" <abolen(at)chat(dot)ru> writes:
> peroon=# INSERT INTO t_foo (foo_value) VALUES (2000);
> NOTICE: fn_foo_ains: Start
> NOTICE: fn_foo_ains: End
> NOTICE: fn_bar_ains: Start
> NOTICE: fn_bar_ains: End

Looking at the code, it seems that all AFTER triggers are implicitly
handled as DEFERRED triggers, ie, they're queued up and executed at
end of statement. This seems wrong to me --- DEFERRED mode is useful,
certainly, but it shouldn't be the only form of AFTER trigger.

Also, it'd seem to me that DEFERRED mode ought to mean defer till end
of transaction, not just end of statement...

Jan, any comments here?

regards, tom lane