PostgreSQL parameter statement_timeout

#postgres #database #devops #sql

The statement_timeout is configuration parameter of PostgreSQL.

It sets the length of time before a statement automatically time out.

Usage examples.

#1 Example. How to cancel long running SELECT query using the PostgreSQL parameter statement_timeout?

postgres=# select version();
                                                           version
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)


postgres=# show statement_timeout;
 statement_timeout
-------------------
 0
(1 row)

postgres=# set statement_timeout='2s';
SET

postgres=# SELECT * FROM generate_series(1,50000000000);
ERROR:  canceling statement due to statement timeout

#2 Example. How to cancel long running UPDATE query using the PostgreSQL parameter statement_timeout?

postgres=# create table abc(a bigint);
CREATE TABLE

postgres=# set statement_timeout=0;
SET

postgres=# insert into abc SELECT * FROM generate_series(1,10000000);
INSERT 0 10000000

postgres=# set statement_timeout='2s';
SET

postgres=# update abc set a=a+2;
ERROR:  canceling statement due to statement timeout

#3 Example. How to cancel long running DELETE query using the PostgreSQL parameter statement_timeout?

postgres=# show statement_timeout;
 statement_timeout
-------------------
 2s
(1 row)

postgres=# select count(1) from abc;
  count
----------
 10000000
(1 row)

postgres=# delete from abc;
ERROR:  canceling statement due to statement timeout
postgres=# select count(1) from abc;
  count
----------
 10000000
(1 row)

postgres=#

#4 Example. How to cancel long running INSERT query using the PostgreSQL parameter statement_timeout?

postgres=# select count(1) from abc;
  count
----------
 10000000
(1 row)

postgres=# insert into abc select * FROM generate_series(1,10000000);
ERROR:  canceling statement due to statement timeout
postgres=# select count(1) from abc;
  count
----------
 10000000
(1 row)

#5 Example. How to cancel long running CREATE INDEX query using the PostgreSQL parameter statement_timeout?

postgres=# show statement_timeout;
 statement_timeout
-------------------
 2s
(1 row)

postgres=# create index abc_idx on abc(a);
ERROR:  canceling statement due to statement timeout
postgres=#