2

My postgresql 9.3 logs are sprinkled with entries like these (redacted to protect the guilty):

STATEMENT:  INSERT INTO table (key, value) VALUES ($1, $2)
ERROR:  duplicate key value violates unique constraint "table_pkey"
DETAIL:  Key (key)=(xyz) already exists.
STATEMENT:  INSERT INTO table (key, value) VALUES ('xyz', '{...json...}')

I know the source of the problem (a race condition where two app servers do the same work) and that code gracefully handles the insertion failure, but for the life of me I can't seem to get PostgreSQL to stop logging the statement which generated the error to begin with.

I've set log_statements for this db to 'none' (which persists across connections):

show log_statement;
 log_statement 
---------------
none
(1 row)

But to no avail. These entries still keep hitting the logs. I wouldn't mind except the "values" from the logged statement are JSON blobs which aren't tiny. How can I keep PostgreSQL from logging whenever an insert violates the unique constraint?

migrated from serverfault.com Mar 21 '15 at 11:19

This question came from our site for system and network administrators.

  • 1
    log_error_verbosity ? – Milen A. Radev Mar 20 '15 at 19:41
  • @MilenA.Radev correct. "alter database db_name set log_error_verbosity to 'terse';" as an answer and I'll accept it. – notpeter Mar 20 '15 at 19:56
  • log_min_messages and log_min_error_statement: postgresql.org/docs/current/static/… – a_horse_with_no_name Mar 21 '15 at 11:36
  • log_statement does not do the trick because it logs statements irregardless of errors. You want to modify what's logged in case of an error. Milen and a_horse provided the relevant settings for this. – Erwin Brandstetter Mar 21 '15 at 18:04
3

The amount of details logged is controlled by log_error_verbosity. Setting it to TERSE will exclude DETAIL, HINT, QUERY, and CONTEXT error information:

ALTER DATABASE db_name SET log_error_verbosity to 'TERSE';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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