Обсуждение: Odd config issue, cannot set log_min_duration_statement

All;

I have a client running PostgreSQL v10

I set:

log_min_duration_statement = 0

and restarted the cluster but if I connect via psql and check it's still disabled

postgres=# show log_min_duration_statement ;
 log_min_duration_statement
----------------------------
 -1
(1 row)

I tried an alter system, now if I cat the auto.conf file i see:

$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
log_min_duration_statement = '0'

However even after another restart It's still disabled:

postgres=# show log_min_duration_statement ;
 log_min_duration_statement
----------------------------
 -1
(1 row)

I grep'ed for the setting in the config file just in case:

$ grep log_min_duration_statement postgresql.conf
log_min_duration_statement = 0          # -1 is disabled, 0 logs all statements

I'm stumped... Thoughts?

Thanks in advance

S Bob <sbob@quadratum-braccas.com> writes:
> I set:
> log_min_duration_statement = 0
> and restarted the cluster but if I connect via psql and check it's still 
> disabled

If you just did "SET" then it'd only affect the current session.

> I tried an alter system, now if I cat the auto.conf file i see:
> $ cat postgresql.auto.conf
> # Do not edit this file manually!
> # It will be overwritten by the ALTER SYSTEM command.
> log_min_duration_statement = '0'

Hmm, that should have worked.  What do you see in

select * from pg_settings where name = 'log_min_duration_statement';

(the "source..." columns are important here)

            regards, tom lane

S Bob <sbob@quadratum-braccas.com> writes:
> I set:
> log_min_duration_statement = 0
> and restarted the cluster but if I connect via psql and check it's still
> disabled

If you just did "SET" then it'd only affect the current session.

> I tried an alter system, now if I cat the auto.conf file i see:
> $ cat postgresql.auto.conf
> # Do not edit this file manually!
> # It will be overwritten by the ALTER SYSTEM command.
> log_min_duration_statement = '0'

Hmm, that should have worked.  What do you see in

select * from pg_settings where name = 'log_min_duration_statement';

(the "source..." columns are important here)

regards, tom lane

> On Jan 14, 2021, at 7:12 PM, S Bob <sbob@quadratum-braccas.com> wrote:
>
> and restarted the cluster but if I connect via psql and check it's still disabled

Sounds to me like maybe the config file PG loads is not where you think it is...

On 1/14/21 7:31 PM, Tom Lane wrote:
> S Bob <sbob@quadratum-braccas.com> writes:
>> I set:
>> log_min_duration_statement = 0
>> and restarted the cluster but if I connect via psql and check it's still
>> disabled
> If you just did "SET" then it'd only affect the current session.
>
>> I tried an alter system, now if I cat the auto.conf file i see:
>> $ cat postgresql.auto.conf
>> # Do not edit this file manually!
>> # It will be overwritten by the ALTER SYSTEM command.
>> log_min_duration_statement = '0'
> Hmm, that should have worked.  What do you see in
>
> select * from pg_settings where name = 'log_min_duration_statement';
>
> (the "source..." columns are important here)
>
>             regards, tom lane



postgres=# \x
Expanded display is on.
postgres=# select * from pg_settings where name = 
'log_min_duration_statement';
-[ RECORD 1 
]---+-----------------------------------------------------------------------
name            | log_min_duration_statement
setting         | -1
unit            | ms
category        | Reporting and Logging / When to Log
short_desc      | Sets the minimum execution time above which statements 
will be logged.
extra_desc      | Zero prints all queries. -1 turns this feature off.
context         | superuser
vartype         | integer
source          | user
min_val         | -1
max_val         | 2147483647
enumvals        |
boot_val        | -1
reset_val       | -1
sourcefile      |
sourceline      |
pending_restart | f

On 1/14/21 7:37 PM, Scott Ribe wrote:
>> On Jan 14, 2021, at 7:12 PM, S Bob <sbob@quadratum-braccas.com> wrote:
>>
>> and restarted the cluster but if I connect via psql and check it's still disabled
> Sounds to me like maybe the config file PG loads is not where you think it is...
>

Actually I thought about that too, verified it, and I also changed the 
shared_buffers setting at the same time I tried to change 
log_min_duration_statement, the shared_buffers change was successful

S Bob <sbob@quadratum-braccas.com> writes:
> On 1/14/21 7:31 PM, Tom Lane wrote:
>> Hmm, that should have worked.  What do you see in
>> select * from pg_settings where name = 'log_min_duration_statement';

> source          | user

IIRC, that means you've applied a setting via ALTER USER,
which'd override the default from the config file.

            regards, tom lane

S Bob <sbob@quadratum-braccas.com> writes:

On 1/14/21 7:31 PM, Tom Lane wrote:

Hmm, that should have worked.  What do you see in
select * from pg_settings where name = 'log_min_duration_statement';

source          | user

IIRC, that means you've applied a setting via ALTER USER,
which'd override the default from the config file.
			regards, tom lane

Yup, that's the last thing I tried:

postgres=# ALTER SYSTEM SET log_min_duration_statement = 0;

and it added the entry in the postgresql.auto.conf file:

$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
log_min_duration_statement = '0'

But... it's still -1, even after another restart

S Bob <sbob@quadratum-braccas.com> writes:
> On 1/14/21 7:53 PM, Tom Lane wrote:
>> IIRC, that means you've applied a setting via ALTER USER,
>> which'd override the default from the config file.

> Yup, that's the last thing I tried:
> postgres=# ALTER SYSTEM SET log_min_duration_statement = 0;

But somewhere you did "ALTER *USER* yourself", and that's
overriding the ALTER SYSTEM for your sessions.  ALTER USER RESET
should get rid of that.

            regards, tom lane