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.

Yes unless triggers/rules are in volved..
You will either be at risk of a race condition or more likely your
insert will try the dupe insert and block waiting for another session
that's already inserted same row to commit/abort. And if other guy does
commit then you will raise a dupe key exception.

Your code is wise trying to avoid a dupe insert if the row is already
existing and visible but you will still need to trap the dupe key
exception when you get one which will happen eventually if there will
be other sessions trying this same insert.
Every statement you run is a transaction of its own but you are far more
at risk of testing negative for an existing row, proceeding to try the
insert but then hanging because there is the same insert already
pending... if there are longer running complex transactions involved.

Suppose...

session A
begin;
insert into your table key=1
... do more work here...

meanwhile...
session B
test for row and I don't see it
try insert and hang here till commit/abort of session A

session A
commit

session B
Doh!! dupe key error

HTH
--
Jerry Sievers
Postgres DBA/Development Consulting
e: ***@comcast.net
p: 312.241.7800

This code can still fail with a unique violation, yes, as the select can
occur in both transactions then the insert in both.
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