How do I configure PostgreSQL 8.3 to log bind parameters?

Question

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?

Answer 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.

Answer 2

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.

• devNoise: This is the right suggestion, but unfortunately I can't upgrade the database at this point.