Thread: PostgreSQL trigger execution order
Hi All,
I posted a few questions earlier last week about how triggers are
executed and wanted to expand on them, if possible.
I have a table with 4 AFTER INSERT triggers defined for a table.
For example purposes lets call them A,B,C,D.
I know that they will execute in alphabetical order as per the
PostgreSQL docs.
However, on occasion, trigger B will cause another insert in the same
table. This, in turn, causes all the AFTER INSERT triggers to run again
for the newly inserted row from the first invocation of trigger B.
I have all the appropriate stop condition in place to avoid a never
ending cycle of trigger invocations.
My question is the following:
In what order will the triggers be executed?
Will it be:
INSERT row
INVOKE TRIGGER A (First call)
INVOKE TRIGGER B (First call) -> INSERT row
INVOKE TRIGGER A (Second call)
INVOKE TRIGGER B (let say no new insert)
INVOKE TRIGGER C (Second call)
INVOKE TRIGGER D (Second call)
INVOKE TRIGGER C (First call)
INVOKE TRIGGER D (First call)
Or will it be:
INVOKE TRIGGER A (First call)
INVOKE TRIGGER B (First call) -> INSERT row and wait...
INVOKE TRIGGER C (First call)
INVOKE TRIGGER D (First call)
INVOKE TRIGGER A (Second call)
INVOKE TRIGGER B (let say no new insert)
INVOKE TRIGGER C (Second call)
INVOKE TRIGGER D (Second call)
My last set of questions confirmed that triggers aren't run
multi-threaded and hence cannot be run in parallel, so I'm assuming one
of the above scenarios must happen. After putting a bunch of RAISE
NOTICEs in my triggers it would appear as though the former scenario is
happening but I'm not 100% sure.
Any help would be much appreciated.
Kindest regards,
Sebastian
--
Sebastian Ritter
Software Manager
sebastian@campbell-lange.net
Campbell-Lange Workshop
www.campbell-lange.net
0207 6311 555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928
On 6 Jul 2010, at 11:33, Sebastian Ritter wrote: > I have a table with 4 AFTER INSERT triggers defined for a table. > > For example purposes lets call them A,B,C,D. > > I know that they will execute in alphabetical order as per the > PostgreSQL docs. > > However, on occasion, trigger B will cause another insert in the same > table. This, in turn, causes all the AFTER INSERT triggers to run again > for the newly inserted row from the first invocation of trigger B. ... > My question is the following: > > In what order will the triggers be executed? > > Will it be: > > INSERT row > INVOKE TRIGGER A (First call) > INVOKE TRIGGER B (First call) -> INSERT row > INVOKE TRIGGER A (Second call) > INVOKE TRIGGER B (let say no new insert) > INVOKE TRIGGER C (Second call) > INVOKE TRIGGER D (Second call) > INVOKE TRIGGER C (First call) > INVOKE TRIGGER D (First call) > > Or will it be: > > INVOKE TRIGGER A (First call) > INVOKE TRIGGER B (First call) -> INSERT row and wait... Wait for what exactly? You seem to expect some kind of external event here. > INVOKE TRIGGER C (First call) > INVOKE TRIGGER D (First call) > > INVOKE TRIGGER A (Second call) > INVOKE TRIGGER B (let say no new insert) > INVOKE TRIGGER C (Second call) > INVOKE TRIGGER D (Second call) > > My last set of questions confirmed that triggers aren't run > multi-threaded and hence cannot be run in parallel, so I'm assuming one > of the above scenarios must happen. I think I'll expand a bit on my previous explanation: The situation is that for every database connection a new (single-threaded) postgres process is spawned. On each connection transactions are processed in sequence. You can't have multiple transactions in parallel on the same connection,as processes are single-threaded. Transactions can't span multiple processes (or connections), I suppose becauseit would be very hard (impossible?) to guarantee integrity if you'd go that route. With that knowledge, your second scenario cannot happen. > After putting a bunch of RAISE > NOTICEs in my triggers it would appear as though the former scenario is > happening but I'm not 100% sure. I'm quite confident it does. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4c32fe12286212337248725!
Hi Alban, Thanks again for your response. On Tue, Jul 06, 2010 at 11:57:14AM +0200, Alban Hertroys wrote: > On 6 Jul 2010, at 11:33, Sebastian Ritter wrote: > > > In what order will the triggers be executed? > > > > Will it be: > > > > INSERT row > > INVOKE TRIGGER A (First call) > > INVOKE TRIGGER B (First call) -> INSERT row > > INVOKE TRIGGER A (Second call) > > INVOKE TRIGGER B (let say no new insert) > > INVOKE TRIGGER C (Second call) > > INVOKE TRIGGER D (Second call) > > INVOKE TRIGGER C (First call) > > INVOKE TRIGGER D (First call) > > > > Or will it be: > > > > INVOKE TRIGGER A (First call) > > INVOKE TRIGGER B (First call) -> INSERT row and wait... > > Wait for what exactly? You seem to expect some kind of external event here. 'wait...' for the first invocation of trigger C and D to finish before starting the second round of invocations A-D. > > > INVOKE TRIGGER C (First call) > > INVOKE TRIGGER D (First call) > > > > INVOKE TRIGGER A (Second call) > > INVOKE TRIGGER B (let say no new insert) > > INVOKE TRIGGER C (Second call) > > INVOKE TRIGGER D (Second call) > > As you suggested in your response, I think the first scenario is happening. Regards, Seb
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