Brodie Thiesfield <brofield+pgsql@gmail.com> writes:
> Essentially, I have two processes connecting to a single PG database
> and simultaneously issuing the following statements:
> BEGIN;
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> DELETE FROM licence_properties WHERE key = xxx;
> INSERT INTO licence_properties ... values with key = xxx;
> COMMIT
You mean they both want to insert the same key?
> One of these processes is getting to the INSERT and failing with
> duplicate key error.
> ERROR: duplicate key value violates unique constraint
If they both insert the same key, this is what *must* happen. Surely
you don't expect both to succeed, or one to fail and not tell you.
> The DELETE should prevent this duplicate key error from occurring. I
> thought that the ISOLATION LEVEL SERIALIZABLE would fix this problem
> (being that the second process can see the INSERT from the first
> process after it has done the DELETE), but it doesn't.
I think you've got the effects of SERIALIZABLE backward, but in any
case SERIALIZABLE does not affect uniqueness checks. Unique is unique.
regards, tom lane