current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 3 down vote favorite
1

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?

share|improve this question
up vote 1 down vote accepted

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.

share|improve this answer
up vote 0 down vote

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.

share|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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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