Thread: unique constraint violations
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.
Schema | public
Name | executehandleunique
Result data type | bigint
Argument data types | querystring character varying
Type | normal
Volatility | volatile
Owner | sysdba
Language | plpgsql
Source code | --
: -- This function executes an arbitrary query and catches any unique violation
: -- that may occur without causing the database transaction to get rolled back.
: -- Warning: Don't use this for queries that may be generated from user input
: -- as it can create a security problem.
: --
: BEGIN
: BEGIN
: RAISE NOTICE 'executing "%"', querystring;
: execute querystring;
: EXCEPTION
: WHEN unique_violation THEN
: return 2; -- status value 2 indicates unique constraint violation occurred
: END;
: RETURN 0; -- status 0 indicates success
: END;
:
Description |
Our application is a closed system, meaning that it does not accept input from untrusted sources.
Is this function safe? (Knowing that we do not handle any untrusted input.)
Are there any potential problems with this approach?
Is there a better way to avoid unique constraint errors and the transaction rollback?
Thank you.
Thank you.
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.
Thank you for the suggestion Steven.
Originally I did implement a solution using savepoints and that worked as a way to keep all the work done on the transaction leading up to the constraint violation, but errors would still show up in the Postgres log. With this new function approach there are no errors in the log.
As far as the serialization, that might be an option, but it probably would require substantial work to redesign our db access patterns.Originally I did implement a solution using savepoints and that worked as a way to keep all the work done on the transaction leading up to the constraint violation, but errors would still show up in the Postgres log. With this new function approach there are no errors in the log.
Also we're still currently on 8.4 (but hope to upgrade soon!)
On Wed, Jun 26, 2013 at 2:39 PM, Steven Schlansker <steven@likeness.com> wrote:
We have solved this problem by leveraging the new SSI feature in Postgres 9.1 (http://wiki.postgresql.org/wiki/SSI)
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 misleading to our support folks because they look like errors but in fact they are handled (usually) by retries in the code and don't cause any real problems. We'd like these to be handled without causing errors to show up in the logs.
>
By running your insert / update inside of a serializable transaction, instead of getting unique failures, you get serialization failures. These are "expected" and we just retry them. Much less confusing log clutter, and no need to differentiate between "expected" and "unexpected" unique violations.The approach we took wouldn't solve this problem for you. Serialization failures also require restarting the transaction.
> 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 to be redone.
>
> As a work around for this, I have proposed the following function which will execute an arbitrary query (with no results returned) and catch a unique constraint violation without causing errors to show up in the postgres log or the transaction to get rolled back. Now what the code does is to call this function with the query to execute. If a unique constraint violation happens, the caller gets a non-zero return code back and this can be handled but the DB transaction does not get rolled back.
>
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 mention it.