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?
> 3) Will the behavior be different if the invoking processes have a
> transaction in progress?
1) The top-level query controls FOUND; so yes
2) Impossible - functions always execute in a transaction. Actually,
everything executes in a transaction the choice is whether you want to
auto-commit.
3) see #2
Still not super fluent wrt concurrency reasoning but here it goes:
Since we are dealing with MVCC here, and the default READ COMMITTED
isolation level, the data that each session/statement would see would be
stable and not include any INSERTs concurrently performed by the other.
Thus if two sessions try to simultaneously insert the same (k,v) the one
that commits second will error.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Atomicity-of-INSERT-INTO-SELECT-WHERE-NOT-EXISTS-tp5816655p5816668.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
