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.
