Odd config issue, cannot set log_min_duration_statement

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Odd config issue, cannot set log_min_duration_statement

S Bob

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



Reply | Threaded
Open this post in threaded view
|

Re: Odd config issue, cannot set log_min_duration_statement

Tom Lane-2
S Bob <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Odd config issue, cannot set log_min_duration_statement

franklin ferreira de lima
In reply to this post by S Bob
Centro de educação integral 



Enviado do meu smartphone Samsung Galaxy.

-------- Mensagem original --------
De: Tom Lane <[hidden email]>
Data: 14/01/21 23:31 (GMT-03:00)
Para: S Bob <[hidden email]>
Assunto: Re: Odd config issue, cannot set log_min_duration_statement

S Bob <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Odd config issue, cannot set log_min_duration_statement

Scott Ribe-2
In reply to this post by S Bob

> On Jan 14, 2021, at 7:12 PM, S Bob <[hidden email]> 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...



Reply | Threaded
Open this post in threaded view
|

Re: Odd config issue, cannot set log_min_duration_statement

S Bob
In reply to this post by Tom Lane-2

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

> S Bob <[hidden email]> 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




Reply | Threaded
Open this post in threaded view
|

Re: Odd config issue, cannot set log_min_duration_statement

S Bob
In reply to this post by Scott Ribe-2

On 1/14/21 7:37 PM, Scott Ribe wrote:
>> On Jan 14, 2021, at 7:12 PM, S Bob <[hidden email]> 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





Reply | Threaded
Open this post in threaded view
|

Re: Odd config issue, cannot set log_min_duration_statement

Tom Lane-2
In reply to this post by S Bob
S Bob <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Odd config issue, cannot set log_min_duration_statement

S Bob


On 1/14/21 7:53 PM, Tom Lane wrote:
S Bob [hidden email] 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


Reply | Threaded
Open this post in threaded view
|

Re: Odd config issue, cannot set log_min_duration_statement

Tom Lane-2
S Bob <[hidden email]> 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