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! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
