Basic question regarding insert

From: Anil Menon <gakmenon(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Basic question regarding insert
Date: 2014-10-31 10:24:29
Message-ID: CAHzbRKchCEHU=QgPpb5o3gTOqFRC=HCA8OUhA1HemZ1kbr6SSw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2014-10-31 13:31:04 Re: PostgreSQL on tablet grade SSD ?
Previous Message Torsten Förtsch 2014-10-31 09:55:47 Re: Planet Postgres