Although the value of the "statement_timeout" parameter in the config file in one of my Postgresql databases is "0", there is a timeout period in my database. What is the reason for this?
config file: enter image description here
When I check the timeout period:
Note: I have changed the timeout on a user basis for now. But why do I see a timeout period of "90 seconds" even though my user does not have any timeout period and this value is "0" in my config file?
postgresql version: 13
If you want to know why you have a certain parameter setting, query pg_settings:
SELECT setting, unit, source, sourcefile, sourceline
FROM pg_settings
WHERE name = 'statement_timeout';
Then you know where you have to change the setting.
source = database. Based on this result, I think I normally need to make changes in the postgresql.conf file. However, here my statement_timeout value is set to the default, which is "0". What could be the source of this timeout other than the config file? Where else can we adjust it at the database level? Thanks.
ALTER DATABASE mydb SET statement_timeout=90. You just need to run alter database mydb reset statement_timeout;ALTER DATABASE and checked again, but it's still 9000ms. Currently, I suspect the postgresql.auto.conf file. This server was normally a "secondary replica server." Later, it was made the primary server with "Failover". This server's postgresql.auto.conf file also contains old replication parameters. However, there is no parameter entered for timeout.
ALTER DATABASE ... RESET ..., you need to start a new session to see the effect. then you can run my query again and see from where you get the setting now.Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions.
The value of the statement_timeout parameter could be set as default in a specific ROLE, check this out:
ALTER ROLE <specific_role> SET statement_timeout = 90;
