On 10/31/2014 03:24 AM, Anil Menon wrote:
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?
Not sure but you might want to look at STATEMENT vs ROW triggers in
combination with multi-row form of INSERT:
http://www.postgresql.org/docs/9.3/interactive/sql-createtrigger.html
A trigger that is marked FOR EACH ROW is called once for every row that
the operation modifies. For example, a DELETE that affects 10 rows will
cause any ON DELETE triggers on the target relation to be called 10
separate times, once for each deleted row. In contrast, a trigger that
is marked FOR EACH STATEMENT only executes once for any given operation,
regardless of how many rows it modifies (in particular, an operation
that modifies zero rows will still result in the execution of any
applicable FOR EACH STATEMENT triggers).
http://www.postgresql.org/docs/9.3/interactive/sql-insert.html
To insert multiple rows using the multirow VALUES syntax:
INSERT INTO films (code, title, did, date_prod, kind) VALUES
('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
