* Lincoln Yeoh:
>>If you use serializable transactions in PostgreSQL 9.1, you can
>>implement such constraints in the application without additional
>>locking. However, with concurrent writes and without an index, the rate
>>of detected serialization violations and resulting transactions aborts
>>will be high.
>
> Would writing application-side code to handle those transaction aborts
> in 9.1 be much easier than writing code to handle transaction
> aborts/DB exceptions due to unique constraint violations? These
> transaction aborts have to be handled differently (e.g. retried for X
> seconds/Y tries) from other sort of transaction aborts (not retried).
There's a separate error code, so it's easier to deal with in theory.
However, I don't think that's sufficient justification for removing the
unique constraints.
> Otherwise I don't see the benefit of this feature for this
> scenario. Unless of course you get significantly better performance by
> not having a unique constraint.
Performance is worse.
> If insert performance is not an issue and code simplicity is
> preferred, one could lock the table (with an exclusive lock mode),
> then do the selects and inserts, that way your code can assume that
> any transaction aborts are due to actual problems rather than
> concurrency. Which often means less code to write :).
Choosing the right lock is a bit tricky because you usually want to
block INSERTs only. Explicit locks on a hash of the unique column,
using pg_advisory_xact_lock, are often an alternative.
--
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