Resolve PostgreSQL execution timeout

Time:2021-9-26

Use background

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.

Parameter description

statement_timeout
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.

1idle_in_transaction_session_timeout

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.

Modification method

Find configuration

Find the location of the PostgreSQL configuration file through the command and edit it with VI.

1

2find / -name “postgresql.conf”vi /var/lib/pgsql/9.6/data/postgresql.conf

modify parameters

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.

:/statement_timeout

Restart configuration

Find PG with the following command_ CTL, and then execute PG_ CTL reload reloads the configuration.

1

2find / -name “pg_ctl”/usr/pgsql-9.6/bin/pg_ctl reload

PG_ CTL usage

Start the server

Start 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

Stop server

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

background

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)

example

begin;
……
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;
……
end;

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 $$
declare
begin
set local statement_timeout=’100ms’;
return query select count(*) as cnt, id from a where id<$1 group by id;
end;
$$ 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.

https://www.postgresql.org/me…

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.

Parameter level

1. Instance level

modify

1postgresql.conf

2. Library level

1alter database dbname set parameter=?;

3. User level

1alter role rolname set parameter=?;

4. Session level

1set parameter=?;

5. Transaction level

begin;
set local parameter=?;
….
end;

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

Recommended Today

Supervisor

Supervisor [note] Supervisor – H view supervisor command help Supervisorctl – H view supervisorctl command help Supervisorctl help view the action command of supervisorctl Supervisorctl help any action to view the use of this action 1. Introduction Supervisor is a process control system. Generally speaking, it can monitor your process. If the process exits abnormally, […]