Re: Concurrent Inserts - Mailing list pgsql-general

From Albe Laurenz
Subject Re: Concurrent Inserts
Date
Msg-id A737B7A37273E048B164557ADEF4A58B3658E2B5@ntex2010i.host.magwien.gv.at
Whole thread Raw
In response to Concurrent Inserts  (sri harsha <sriharsha9992@gmail.com>)
Responses Re: Concurrent Inserts  (sri harsha <sriharsha9992@gmail.com>)
List pgsql-general
sri harsha wrote:
>    Is there any way to stop concurrent inserts to happen on a single table ??
> 
> Query 1 : INSERT INTO TABLE_A SELECT * FROM TABLE1;
> Query 2 : INSERT INTO TABLE_A SELECT * FROM TABLE2;
> Query 3 : SELECT * FROM TABLE_A;
> 
> Assume i have the above queries. Query 1 and Query 3 can occur concurrently . If one insert is taking
> place , the other should wait. How do i achieve this ??

Is that a typo and you really mean "query 1 an query 2" above?

Why would you want to prevent concurrent inserts?
Maybe you don't really need to hobble your performance like that.

If you really want that, it is easy with table locks.
Your INSERT could look like that:

BEGIN;
LOCK table_a IN EXCLUSIVE MODE;
INSERT INTO table_a ...
COMMIT;

>    How do the concurrent inserts take place in postgres ?? Is data stored temporarily for both queries
> separately and then written into the table ??

No, it is written to the table concurrently.
A table is organized in pages of 8KB each, and there is no problem with writing to
different pages concurrently.

Yours,
Laurenz Albe

pgsql-general by date:

Previous
From: sri harsha
Date:
Subject: Concurrent Inserts
Next
From: sri harsha
Date:
Subject: Re: Concurrent Inserts