Atomic Insert in PostgreSQL | Part 2

The solution presented in the previous article has the downside of locking the entire table my_table. This becomes a problem when criterion is made unique via a unique constraint and a time-consuming operation (e.g., a search) follows in the same transaction after the function call: In that case, all processes wanting to perform a findOrCreate – regardless of the value of criterion – have to wait for the first transaction to complete, since only at that point can the uniqueness of the column be guaranteed.

Ideally, parallel transactions with different values for criterion should be able to proceed without interference. The following approach, suggested by Martin Heistermann and Stephan Luderitz, solves this problem elegantly:

CREATE OR REPLACE FUNCTION findOrCreate(
  IN criterion_in text,
  OUT record_id integer, OUT is_new bool) AS
$$
DECLARE
BEGIN
    INSERT INTO
      my_table (criterion)
    VALUES
      (criterion_in)
    RETURNING id INTO record_id;
    SELECT true INTO is_new;
EXCEPTION
    WHEN unique_violation THEN
        SELECT false, id into is_new, record_id FROM my_table 
        WHERE criterion=criterion_in;
END
$$ language plpgsql;

As described in this presentation (PDF), PostgreSQL allows parallel inserts of different values via findOrCreate, but when two transactions try to insert the same value, it forces the second one to wait for the first to complete before proceeding with its insert.

If the (potentially expensive) search in the transaction occurs after findOrCreate, it no longer needs to be executed, because findOrCreate uses the exception to find the row newly inserted by the first transaction and can continue with those results right away.