Re: On duplicate ignore

* 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(at)bfk(dot)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