* 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@xxxxxx> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
