1

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.

3
  • 1
    There will definitely be an error if you put manu_confuse = foo into postgresql.conf, unless you forget to reload. Note also that settings with ALTER SYSTEM and ALTER DATABASE etc. override postgresql.conf. Look at pg_settings to see where your current setting originates. – Laurenz Albe Jan 7 at 8:43
  • manu_confuse = foo does get errored, yes. But not manu_confuse.foo = bar, because no sub-system "manu_confuse" will ever be loaded to try to initialize its parameters. And yes, that pg_setting table is the key to the confusion. Very handy to know that pg_setting table and check that there aren't some old misconfigurations stuck in there while you're pulling your hair out why postgresql.conf changes don't do anything! – Gunther Schadow Jan 8 at 17:09
  • Seems like everything is working as it should and you got confused somewhere. – Laurenz Albe Jan 11 at 7:14
1

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.

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.