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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
