Recently, when using PostgreSQL and executing some database transactions, the problems of statement timeout and idle in transaction timeout occurred successively, resulting in the failure of database operation.
After research and search, PostgreSQL has relevant configurations about SQL statement execution timeout and transaction execution timeout, and the default timeout is 10000 milliseconds, that is, 10 seconds, which will lead to the execution failure of tasks with a slightly longer execution time. You can modify the default configuration by modifying the PostgreSQL server configuration file.
statement_ Timeout is used to control the execution time of statements in PostgreSQL. The unit is Ms.
$ vi postgresql.conf
statement_timeout = 0 # in milliseconds, 0 is disabled
The default is 0, which means that the statement can be executed all the time.
If it is set to 10000, it means that the statement can execute up to 10000ms = 10s.
It is recommended to set to 0 to disable this parameter.
PostgreSQL version 9.6 supports automatic checking and killing idle transaction connections that exceed the specified time. It is used to clean up open transactions in the application code, or dead processes in the system.
idle_ in_ transaction_ session_ Timeout is used in PostgreSQL to control the transaction execution time. The unit is Ms.
$ vi postgresql.conf #idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
The default is 0, which means that the statement can be executed all the time. Fatal: terminating connection due to idle in transaction timeout will be reported.
Find the location of the PostgreSQL configuration file through the command and edit it with VI.
2find / -name “postgresql.conf”vi /var/lib/pgsql/9.6/data/postgresql.conf
In the VI editing interface, you can locate the relevant parameters through the VI search command, modify them to an appropriate time, save and exit.
Find PG with the following command_ CTL, and then execute PG_ CTL reload reloads the configuration.
2find / -name “pg_ctl”/usr/pgsql-9.6/bin/pg_ctl reload
PG_ CTL usage
Start the server
1$ pg_ctl start
An example of starting the server is to wait until the server starts before exiting:
1$ pg_ctl -w start
The server uses port 5433 and runs without fsync, using:
1$ pg_ctl -o “-F -p 5433” start
1$ pg_ctl stop
Using the – M option to stop the server allows the user to control how to shut down the back end.
Restart the server
This command is almost equivalent to stopping the server and then starting it, but PG_ CTL saves and reuses the command line parameters of the last server run. The easiest way to restart the server is:
1$ pg_ctl restart
Restart the server and wait for it to stop and restart:
1$ pg_ctl -w restart
Restart using port 5433 and turn off fsync after restart:
1$ pg_ctl -o “-F -p 5433” restart
Display server status
Here’s from PG_ Example of CTL status output:
$ pg_ctl statuspg_ctl: server is running (pid: 13718)
Command line was:
/usr/local/pgsql/bin/postgres ‘-D’ ‘/usr/local/pgsql/data’ ‘-p’ ‘5433’ ‘-B’ ‘128’
This is the command line invoked in restart mode.
Supplement: PostgreSQL sets the execution timeout of a single SQL – anti avalanche
Set the execution timeout of a single SQL to prevent avalanche.
Generally speaking, the transaction level timeout parameter can be set before SQL initiation, and the SQL execution is completed and reset. (if SQL exits abnormally, transaction level parameters will be reset automatically)
set local statement_time=’100ms’;
select count(*) from a; — If the execution time of this SQL exceeds 100ms, it will automatically exit and roll back the whole transaction
set local statement_timeout to default;
Function level timeout example – statement_ Timeout not available
For example, for this query, we want it to timeout for 100 milliseconds.
1select count(*) as cnt, id from a where id<$1 group by id;
Write it to the function and set the timeout in the function
create or replace function f1(int) returns setof record as $$
set local statement_timeout=’100ms’;
return query select count(*) as cnt, id from a where id<$1 group by id;
$$ language plpgsql strict ;
Call SQL and change to this
1select cnt,id from f1(1) as t(cnt int8, id int);
But this is actually ineffective because of the statement_ Timeout was originally designed for interactive SQL in Postgres. C.
Therefore, plpgsql timeout is required, which needs to be implemented through the plug-in hook.
statement_timeout is measured across an entire interactive command, not
individual commands within a function; and the timeout that applies to
an interactive command is determined at its beginning. So the above
doesn’t do what you think.
1. Instance level
2. Library level
1alter database dbname set parameter=?;
3. User level
1alter role rolname set parameter=?;
4. Session level
5. Transaction level
set local parameter=?;
6. Function level
1alter function fun_name() set parameter=?;
Other timeout controls
1. Idle transaction timeout
1idle_in_transaction_session_timeout = 2h
2. Lock wait timeout
1lock_timeout = 1s
3. Deadlock detection timeout interval
1deadlock_timeout = 1s