Postgresql – Why does PostgreSQL ignore auto_explain.log_analyze = 0 in is postgresql.conf file and how can I turn on auto_explain for all sessions

configurationpostgresql

I have a strange problem with PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180303 (Red Hat 7.3.1-5), 64-bit (that was the complete output from select version();).

My problem is I want to turn on the auto_explain to chase down some problem with triggers that aren't firing. I need this auto explain to give me all the details for a short time to debug this. But not just in my session, I need it to be in all sessions that all application servers access.

I am showing you the problem right here:

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 0
auto_explain.log_analyze = on
auto_explain.log_nested_statements = true
auto_explain.log_triggers = true
auto_explain.log_timing = false

it doesn't do anything unless I set in my session

SET auto_explain.log_min_duration = 0

then it will log the auto explain for that session. But all the application server sessions continue mute.

This is some sort of bug I found. Here is how I know it.

If I put

manu_confuse = foo

into the postgresql.conf file, then no error is thrown.

If I put

auto_explain.foo = bar

into the postgresql.conf file, and not the shared_preload_libraries, then only when I say

LOAD 'auto_explain';

will it tell me that there is no auto_explain.foo property to set.

However, it simply ignores the auto_explain.log_min_duration parameter, no matter what value I give it.

For example, if I say in the .conf file;

auto_explain.log_analyze = 'foobar'

it will complain that the value to log_analyze must be a Boolean.

But when I say in the .conf file

auto_explain.log_min_duration = 'foobar'

it will not complain in any way. It just ignores it. And no matter how often I repeat some bogus assignment of auto_explain.log_analyze, just ignored.

I find that very strange. How has been able to turn auto_explain on for all sessions by default?

PS: A Related question is this, but it is very different. I'm only referencing it because it brought me to this site to ask.

Best Answer

Hmm, in my desperation I further remembered I could try this:

ALTER SYSTEM auto_explain.log_min_duration = '0s';

and it would accept it. It would also recognize if I am using bogus values. Hm, apparently now also acted on it. Is there some sort of stored settings from ALTER SYSTEM that always overrides what postgresql.conf contains? That could be a reasonable answer.

I noticed more strange behavior that after a restart now it does work initially. The ALTER SYSTEM does not need to be invoked again. But it takes some time to begin the auto_explain logging, the very first command was not logged, but then it was.

Related Question