Trigger firing order - Mailing list pgsql-general

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.