Re: unique constraint violations - Mailing list pgsql-general

On Jun 26, 2013, at 11:04 AM, pg noob <pgnube@gmail.com> wrote:

>
> Hi all,
>
> There are some places in our application where unique constraint violations are difficult to avoid due to
multithreading.
> What we've done in most places to handle this is to retry in a loop.
>
> Generally it starts by checking if a value already exists, if not - try to insert it, which may cause a unique
violation,and if a constraint violation occurs, retrying the process again. 
>
> The trouble that we have with this approach is twofold.
> First, it causes errors to show up in the Postgres log about unique constraint violations.  These errors are
misleadingto our support folks because they look like errors but in fact they are handled (usually) by retries in the
codeand don't cause any real problems.  We'd like these to be handled without causing errors to show up in the logs. 
>

We have solved this problem by leveraging the new SSI feature in Postgres 9.1 (http://wiki.postgresql.org/wiki/SSI)

By running your insert / update inside of a serializable transaction, instead of getting unique failures, you get
serializationfailures.  These are "expected" and we just retry them.  Much less confusing log clutter, and no need to
differentiatebetween "expected" and "unexpected" unique violations. 

> Second, in some cases we've done a lot of work on a transaction before hitting a unique constraint violation.
> If this happens the entire transaction gets rolled back and all the work leading up to the constraint violation has
tobe redone. 
>
> As a work around for this, I have proposed the following function which will execute an arbitrary query (with no
resultsreturned) and catch a unique constraint violation without causing errors to show up in the postgres log or the
transactionto get rolled back.  Now what the code does is to call this function with the query to execute.  If a unique
constraintviolation happens, the caller gets a non-zero return code back and this can be handled but the DB transaction
doesnot get rolled back. 
>

The approach we took wouldn't solve this problem for you.  Serialization failures also require restarting the
transaction.


You could also consider savepoints as a more lightweight way of undoing the "bad" updates --
http://www.postgresql.org/docs/9.2/interactive/sql-savepoint.html


Sounds like you might have a slightly different use case and our approach won't help you much, but I figured I'd
mentionit.