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

From: Jim Garrison <jim(dot)garrison(at)nwea(dot)org>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Atomicity of INSERT INTO ... SELECT ... WHERE NOT EXISTS ...
Date: 2014-08-27 22:22:43
Message-ID: 391418bb91fe4570a2fe8bda3281b976@BN1PR06MB839.namprd06.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?
3) Will the behavior be different if the invoking processes have a transaction in progress?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rich Shepard 2014-08-27 22:31:34 UPDATE table: Syntax to Remove Terminal '\n'
Previous Message Adrian Klaver 2014-08-27 21:37:42 Re: error restarting DB