Re: PostgreSQL trigger execution order

Hi Alban,

I have finally managed to get to the bottom of the problem I was facing.

I thought I'd share my findings, as I managed to waste a lot of time
trying to solve the problem.

As previously mentioned I have several complicated triggers that run after an
insert on a given table. Some of these triggers can, under certain
circumstances, insert yet another row in the same table causing a second
round (or maybe more) of triggers to be fired.

At first I wasn't sure if these ran sequentially as the outcome of an
insert did not follow my linear trace.

It turns out that you are absolutely right in that they are not
multi-threaded and do in fact run sequentially.

However, here is a caveat I was not aware of:

As a personal coding preference I use grouped inserts (multi-inserts,
'inserts by select' - not sure what to call them) over for loops in my
PL/pgSQL functions.

I.E:

INSERT INTO <table_name> (SELECT
value1,
value2,
value3
FROM
<query_with_multiple_rows_returned>);
Instead of

FOR result IN
SELECT
value1,
value2,
value3
FROM
<query_with_multiple_rows_returned>
LOOP
INSERT INTO
<table_name>
VALUES
(result.value1,result.value2,result.value3);
END LOOP;

What I found is that, though the order in which the triggers are fired
is the same, the outcome of calling the two aforementioned statements
differs it terms of what the triggers see at time of invocation.

This is only speculation:

As I understand it, in the former approach all the inserts are
'see-able' by each executed trigger immediately. They are inserted as a
block before any triggers are run.

The triggers are run in the appropriate order but the table already
contains all the inserted rows. This was causing certain rows to be
deleted before their due time as they shouldn't have existed until the
triggers of the previous insert had finished executing.

The latter approach acts exactly as expected, not being able to see the
'future' inserts before their time.

I know this may seem trivial but I would have thought the two statements
were equivalent in every way.

I don't know if this of any use, but I thought I'd post it anyway.

Kindest regards,
Seb