Take the 2-minute tour ×
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.

I have a table that can contain reasonably large BYTEA values (the same could apply to large TEXT values). It looks like this:

CREATE TABLE testtable (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL, -- This is just an example: this could be the PK
    value BYTEA -- This could be TEXT
);

If the application using this table tries to insert two rows using the same name, I get this error in the logs (this is an expected error, of course):

ERROR:  duplicate key value violates unique constraint "testtable_name_key"
STATEMENT:  INSERT INTO testtable(name, value) VALUES ('a', ...something rather long...);

While logging the error is useful, as well as logging the statement (and possibly the value for "name" in this particular context), logging the long BYTEA or TEXT value isn't. In fact, binary data in the logs are escaped in text form (e.g. E'\\377\\327...'), which effectively makes it take even more space than it would in binary form in the database. This can cause more problems than help when the value's size is tens of kB or more.

Is there a way to prevent these values from appearing in the logs when there is an error? I'm still interested in knowing there was an error (and preferably have some information about the statement). Of course, this is not just about this particular error and use case, but any error log that could potentially dump multiple kilobytes of uninteresting data in the logs.

Otherwise, is there any way to truncate the "STATEMENT: INSERT INTO ..." line? Failing that, would there be any way to keep the ERROR: line but to remove the STATEMENT: line altogether?

I've tried to set log_error_verbosity = terse in postgresql.conf, but this doesn't seem to change anything for this.

(I'm also using log_destination = 'syslog' to log to rsyslog, so a solution that limits each log "bundle" to a few lines there would be a reasonable compromise.)

I'm using PostgreSQL 8.4, but if solutions only present in newer versions would also be interesting.

share|improve this question
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.