Re: Basic question regarding insert - Mailing list pgsql-general

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@aklaver.com