3

I'm running into some unique constraint problems with my database. I can't reproduce them, but see error messages from our production system:

exception 'UniqueViolation' with message 
    'SQLSTATE[23505]: Unique violation: 7 ERROR:  
    duplicate key value violates unique constraint...'

When I check the log files, I see the statement causing the exception.

Unfortunately, I don't see what the bind parameters for the SQL statement are.

The documentation for 8.3 seems to indicate that I could log the bind parameters, but I cannot determine how to turn on logging.

Can anybody help me figure out what to change in postgresql.conf?

1

I'm not sure how to make those visible in 8.3 except with this:

log_statement = all

That's going to put a lot of data in to your logs though, so you may not want to keep it turned on for long. Also make sure you have the process ID included in your log_line_prefix if you want to match everything up easily.

|improve this answer|||||
0

One solution would be to upgrade to PostgreSQL 8.4 or higher. Preferably to the current version 9.1.

If I provoke a unique violation in version 8.4, I get this message in the db log:

postgres ERROR: duplicate key value violates unique constraint "foo_uni"
postgres STATEMENT: update x.foo set foo = 'barx' where foo = 'bazx'

(With more details - I display the relevant part here.) As you can see, I get the violating statement including the parameters in the error message. I have no 8.3 cluster available for testing, but I suspect this has been improved since version 8.3 as there were many improvements to error messages and logging.

|improve this answer|||||
  • This is the right suggestion, but unfortunately I can't upgrade the database at this point. – devNoise Dec 22 '11 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.