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
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.
Yes you can hide those messages in the log.
In the calling session, before running the
INSERT statement, issue this statement:
SET log_min_messages TO log;
If you want to still see the
ERROR: part, and hide only the
STATEMENT: part, use another parameter:
SET log_min_error_statement TO log;
Use only session parameters, your application must know which statements will be silenced. Technically, you could make the setting permanent (in postgresql.conf) but it’s not a good idea. In some cases this will be a footgun.