How do I make PostgreSQL not log specific errors?

Question

ERROR: deadlock detected at character 13

My PG log is full of this, but the thing is, I already handle it (after years of frustrations) in my application. I detect them and repeat until they go through. So, there is no longer any reason for me to see these specific "errors", and the only thing they accomplish now is to obscure the remaining actual errors!

Therefore, I wonder if there is some way to prevent PG from logging specifically those "deadlock detected" errors. I don't want other errors of the same "significance" to also be suppressed; only those ones, since I'm aware that they happen and already handle them.

I could not find any such directive in the manual. log_min_messages and log_min_error_statement are both too generic and, if I changed them, would potentially suppress other errors which I haven't figured out how to handle.

The fact that these deadlocks are actually considered/called "errors" make me feel uncomfortable. Even though I do handle them, they seem to be utterly impossible to avoid in real life. Sooner or later, usually when a lot of things are executed at the same time/in parallel, they will happen. But once again, my code detects it and waits and repeats the query over and over until it goes through, so I no longer consider these to be errors that should be logged.

I hope this isn't impossible without me switching from the "plain" text log to the CSV log and then import it back into my database and exclude these errors that way. It seems like this kind of error log should be plaintext file-based, and eventlog (Windows) is like a joke. (Impossible to navigate/extract anything useful from.)

Answer 1

You could write an extension which uses emit_log_hook to suppress the message, by selectively setting edata->output_to_server to false. It will have to be written in C and compiled, which is not trivial to do (especially on Windows).

Here is an example of using emit_log_hook.

Answer 2

You can write your own shell script to view new errors and exclude other errors that you already know about. But IMHO, the dead lock errors are errors. Dead locks are probably caused by unindexed foreign keys or by triggers. You can start by making sure that all of your foreign keys are indexed. But you also need to look at what tables are involved in the dead lock and what triggers those tables have. Postgres seems to be less picky than Oracle about mutating table triggers, but a trigger that is directly changing its underlying table is probably a likely source for a dead lock as any. Or two tables that each have a trigger that changes the other table is as good a guess as any. You should really want to resolve the underlying issue, then your app will be faster and you can look for other areas for improvement.