unique constraint violations

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.

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.

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.