Hi,
I have a very basic question on inserts - I tried to get a good authoritative answer but could not really find one to my satisfaction in the usual places.
TLDR : can (after) insert trigger be run in parallel?
Assume an OLTP environment were a lots of inserts are happening to a transaction (TX) table. These inserts are very small - inserts 3 cols only. No deletes and updates happen to this table.
All inserts to this TX table raise a after insert trigger. The trigger inserts 1 to N new records to a (audit) table AUD. On this table too no deletes and updates are performed.
Most of the time (99% of the time) only 1 record is inserted into AUD table by the trigger for an insert into TX (called an "event tx") , but the other 1% of the time 1000s of new records are inserted into the AUD table for the insert of TX (called "correlate tx").
My question is : does the trigger block new inserts into the TX table till it completes its execution? Or are new inserts to the TX table ( and its trigger) queued up and executed sequentially? or in parallel?
i.e Assume a trigger takes 1000ms to complete, if during the 1000ms it takes to complete the trigger code, if X new records are to be inserted into TX table which raises X new triggers, are these X records blocked till the current trigger completes and then executed one-by-one?
Background : a message queue (MQ) interceptor receives MQ messages in the queue and inserts these messages into the TX table. Most messages are event messages which inserts 1 record in the AUD table through an trigger. One of the message is a correlate message and this requires processing. It performs a query on the received messages and performs a correlation analysis (an single analytical query - thank you Postgres!). The resultset in saved into the AUD table and this could be a few 100 to 1000s of records. Multiple instances of the MQ interceptor can be run - this would be like a new session to the database
Thanks in advance
Anil