Re: Atomicity of INSERT INTO ... SELECT ... WHERE NOT EXISTS ...

On 08/28/2014 06:22 AM, Jim Garrison wrote:
> Given (pseudocode)
>
> CREATE TABLE kvstore (
> k varchar primary key,
> v varchar);
>
> CREATE OR REPLACE FUNCTION store_key_value_pair(k varchar, v varchar) returns boolean as $$
> BEGIN
> INSERT INTO kvstore (k, v)
> SELECT :k, :v
> WHERE NOT EXISTS (select 1 from kvstore where k = :k);
> RETURN FOUND;
> END;
> $$ LANGUAGE plpgsql;
>
> I have a few questions:
>
> 1) Does INSERT statement set FOUND based on whether or not the row was inserted?

> 2) If this is invoked without a transaction in progress, is there any guarantee of atomicity between checking the EXISTS and attempting to insert the row? If this is being executed in two (or more) sessions, can the SELECT succeed but then have the INSERT fail with a duplicate-key exception?

This code can still fail with a unique violation, yes, as the select can
occur in both transactions then the insert in both.

> 3) Will the behavior be different if the invoking processes have a transaction in progress?

No, because all functions run in transactions. There is no such thing as
"not in a transaction" in PostgreSQL (except for a few special system
management commands).

If it's in a SERIALIZABLE transaction instead of the default READ
COMMITTED then it might fail with a serialization failure instead of a
unique violation, but it'll still fail.

Please read the detailed guidance on this problem that already exists:

http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services