SERIALIZABLE isolation level doesn't really conform to the spec
because it doesn't deal with phantoms. The only case I've come across
where this actually matters is when you're implementing some sort of
"insert into table if not yet present" operation. This will typically
result in a unique constraint violation.[*]
Usually, constraint violations are programming errors, but not this
one. It's more like a detected deadlock. Is there a way to tell this
type of constraint violation from other types, so that the transaction
can be restarted automatically (as if there was a deadlock)?
Theoretically, PostgreSQL should detect that the conflicting row
wasn't there when the snapshot for the transaction was taken, and
somehow export this piece of information, but I'm not sure if it's
available to the client.
[*] One way to work around this is to batch inserts and eventually
perform them in a background task which doesn't run in parallel, but
this approach isn't always possible.
--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99