Re: PostgreSQL trigger execution order

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 because it 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!