Postgresql – Limiting PostgreSQL logging

bytealogpostgresql

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.

Best Answer

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;

See docs.

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.

Related Question