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
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 |