Concurrency in Insert query using Postgres WITH statement

Question

WITH values (a_name, a_area ) AS ( VALUES ('thename', 'thearea')),
  t AS (SELECT name FROM area_table, values WHERE name=a_name),
  i AS (INSERT INTO area_table (name, area) 
           SELECT a_name, a_area FROM values WHERE a_name NOT IN (SELECT name FROM t))  
SELECT a_name,a_area FROM values WHERE a_name IN (SELECT name FROM t);

The above query is running fine. I just want to know if it can have concurrency problem, if data is inserted concurrently by two or many people it can have any problems or it is fine? Please help I don't have much knowledge of Postgres CTE's and WITH statements.

Honestly, this strikes me as being too clever. You're stuffing things into a CTE when the only benefit I see is that it gets treated as a transaction. You could just wrap significantly simpler statements in a transaction. It would likely perform better, will be easier for someone else to maintain, and you don't have to question concurrency. If you really want to know, however, you could ask on the PostgreSQL mailing lists; they're very active and have a very good understanding of the internal workings and query planning (the devs watch the list). — Bacon Bits, Jul 05 '15 at 00:57
The question **[Is SELECT or INSERT in a function prone to race conditions?](http://stackoverflow.com/q/15939902/939860)** has been asked before - except that one returns all rows where you only return the ones selected. Is that intentional? — Erwin Brandstetter, Jul 06 '15 at 00:45

score 0 · Answer 1 · edited May 23 '17 at 12:21

if data is inserted concurrently by two or many people [can it] have any problems[?]

Yes, it will have problems. That statement is not safe in the presence of concurrent execution, in that it can raise a unique violation error (if a suitable unique constraint is present) or insert duplicates (if no unique constraint is present).

This fragment:

NOT IN (SELECT name FROM t)

can run concurrently in multiple sessions. Neither session has yet INSERTed a tuple. Even if it had, it would be uncommitted, so the SELECT wouldn't see it in its snapshot. Both instances would return false and thus execute the INSERT.

(also, NOT IN can produce unexpected results if the column is nullable, and can be slow. Use NOT EXISTS or a left-anti-join).

Doing everything in one statement - wCTE or otherwise - doesn't make concurrency issues go away, though it can make it a bit harder to trigger them. Even two simple UPDATEs can have interactions related to their concurrent execution (in the form of deadlocks). It's a common mistake to think that the "atomacity" of ACID means that somehow statements execute in a logical instant. That's just not the case. The property of atomicity refers to a transaction either committing with all changes taking effect, or rolling back with no changes taking effect.

See: How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

Concurrency in Insert query using Postgres WITH statement

1 Answers1