Hello,
In this query:
UPDATE foo
SET allocated_to=?
WHERE id=(SELECT MIN(id) FROM foo WHERE allocated_to IS NULL)
AND allocated_to IS NULL
RETURNING id
Is it guaranteed in any way that there will only be one id allocated and
returned even if multiple clients are executing this query concurrently?
Or is there a possibility that some other client executing this query
(or another query modifying allocated_to) might set allocated_to to
non-NULL and commit right after the inner select finds it as NULL, so
the outer "AND allocated_to IS NULL" will no longer be true, and the
outer query will return nothing?
Thanks.