From: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
---|---|
To: | Sebastian Ritter <sebastian(at)campbell-lange(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org, software(at)campbell-lange(dot)net |
Subject: | Re: PostgreSQL trigger execution order |
Date: | 2010-07-06 09:57:14 |
Message-ID: | 5C57030F-D517-4902-AB25-9BA115180C7A@solfertje.student.utwente.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Arnaud Lesauvage | 2010-07-06 10:02:30 | 'default nextval()' loses schema-qualification in dump ? |
Previous Message | Davor J. | 2010-07-06 09:52:20 | Re: psql \dp equivalent or similar query? |