From: | Paul Millar <paul(dot)millar(at)desy(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to silence constraint violation logging for an INSERT |
Date: | 2011-04-15 15:55:07 |
Message-ID: | 201104151755.08031.paul.millar@desy.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
I've a question regarding unique constraints, which I've tried to describe in
general terms, to keep things simple.
I've working on an application that, amongst other things, may add a row to a
table. This table has a primary key defined over two (of the three) fields,
which forces the combined value to be unique.
An end-user of this system can cause the application to add a row to a table,
based on data supplied by that end-user (a create-like command). It can
happen that end-users repeat themselves: rerunning the same activity with the
same data. Logically, repeating the activity doesn't make sense; the
application should fail the second (and all subsequent) attempts with a
meaningful error message.
When writing the application, there was a deliberate design decision: rather
than a read-modify-write cycle, with the corresponding overhead of locking and
the resulting serialisation, the software simply lets the INSERT fail (due to
the primary-key uniqueness constraint). If this happens, the transaction is
rolled back and an error message returned.
The software can identify whether the problem is due to an end-user repeating
an earlier action by looking at the class code from the SQL error ("23" ==
Constraint Violation). This allows us to return the correct error.
This works fine: the correct error message is reported and the system behaves
as it should. There's one problem: if a user repeats their activity then
PostgreSQL logs the corresponding constraint violation:
ERROR: duplicate key value violates unique constraint [..]
The log files may contain many such messages, depending on the usage-pattern
of the end-user. Including all these messages in the log file is distracting.
The question is: can we suppress the logging of these message .. but allow
other error messages to be logged normally?
Cheers,
Paul.
From | Date | Subject | |
---|---|---|---|
Next Message | salah jubeh | 2011-04-15 16:28:10 | Re: correlated query as a column and where clause |
Previous Message | Tom Lane | 2011-04-15 15:49:35 | Re: correlated query as a column and where clause |