2

I got a newly installed database (PG 9.4.6 / RHEL 7) which logfile grows rapidly.

Most entries are like

2016-03-03 10:33:29.259 CET [56868] user@db STATEMENT:  SELECT field1 AS a1, field2 AS a2, field3 AS a3, field4 AS a4, field5 AS a5, field6 AS a6, MSISDN AS a7, field8 AS a8, RETRY_COUNT AS a9 FROM table WHERE ((field3 <= $1) AND (field8 IS NULL)) ORDER BY field3 ASC LIMIT $2 OFFSET $3
2016-03-03 10:33:29.259 CET [56868] user@db LOG:  EXECUTE MESSAGE STATISTICS
2016-03-03 10:33:29.259 CET [56868] user@db DETAIL:  ! system usage stats:
        !       0.000006 elapsed 0.000000 user 0.000000 system sec
        !       [0.526408 user 0.112125 sys total]
        !       0/0 [0/0] filesystem blocks in/out
        !       0/0 [0/1281] page faults/reclaims, 0 [0] swaps
        !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
        !       0/0 [3516/0] voluntary/involuntary context switches

I read through the documentation and found out, that it should be one of the log_*_stats parameters and despite the missing config entry in the postgresql.conf and postgresql.auto.conf

> egrep "log_.*stats" postgresql.*conf
postgresql.conf:#log_parser_stats = off
postgresql.conf:#log_planner_stats = off
postgresql.conf:#log_executor_stats = off
postgresql.conf:#log_statement_stats = off

the running instance shows that the parameter is set to "on".

> echo "show log_statement_stats" | psql
 log_statement_stats
---------------------
 on

I tried to change it by applying an "ALTER SYSTEM", but it doesn't help. The parameter stays "on".

I also reloaded the database (pg_ctl reload) and restarted it (its a pacemaker resource, so pcs resource restart postgre_db) - But nothing happens.

How do I turn off this setting?

  • What happens if you edit postgresql.conf manually and un-comment those parameters ? (instead of using ALTER TABLE) – a_horse_with_no_name Mar 3 '16 at 9:57
  • Exactly the same. It does not matter, if I remove the comment in postgresql.conf or use the ALTER SYSTEM to add the respective line in postgresql.auto.conf. I also restarted the db, but the result is the same: The setting stays enabled. – Markus Mar 3 '16 at 10:03
  • Do you have the auto_explain module installed? postgresql.org/docs/current/static/auto-explain.html – a_horse_with_no_name Mar 3 '16 at 10:07
  • No, the only installed module is pg_stat_statements. – Markus Mar 3 '16 at 10:09
  • From psql, please issue a \drds command. Do you see log_statement_stats listed? – dezso Mar 3 '16 at 11:23
3

There are multiple levels where you configuration options are set in PostgreSQL:

  1. system defaults
  2. values specified in postgresql.conf or the startup command line
  3. values preset for a database, user (maybe others, too) using, for example, ALTER DATABASE ... SET {option} ...
  4. values set for the current session

Going down the list from 2., you have fewer and fewer options to set. Furthermore, there are some that can only be set by a superuser (log_statement_stats) being one of them.

At the same time, the lower in the list an option is set, the higher its precedence: a session-level setting will override all the upper levels.

Speaking specifically about log_statement_stats, it is possible to set it from a session - but only a superuser can do it. If your application connects with a superuser role (which is not really recommended), it can set this as one of the first things after obtaining the connection. Look for it in your sources.

Ways of changing settings

The numbers refer to the levels listed above.

  1. Supplying defaults at compile time
  2. ALTER SYSTEM - this will store the settings in a postgresql.auto.conf file, which will be read in addition to postgresql.conf.
  3. ALTER DATABASE ... SET ... or ALTER ROLE ... [IN DATABASE database_name] SET .... Some settings can be set on database objects (like tables, functions, etc.), too.
  4. SET \[SESSION\] ... - to change things for the current connection (often dubbed 'session'). There is even a way to change things until the end of the current transaction: SET LOCAL ...
  • Ok, you were on the right track. It was a setting on "database"-Level. It was activated in both databases (postgres and application). Is there a quick way to find out those kind of settings? The show commands gave the correct output, but not the place where this setting is set. – Markus Mar 3 '16 at 12:04
  • \drds [PATRN1 [PATRN2]] list per-database role settings - not sure why it did not show it. Currently, I have no better idea, but will probably check it later. – dezso Mar 3 '16 at 12:26
  • Did you find anything? Today I had to change this setting again on databaselevel and didn't find any hint if it is configured? – Markus Jan 10 '17 at 8:51

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.