Statement and Transaction Timeouts in Postgres and CockroachDB

One of the worst day-to-day things you can do in a database is lock a table or rows for too long. This will cause other requests to wait or fail. In some cases, you will need a DBA to step in and kill the offending session to release the locks.

There are three timeouts on Postgres and CockroachDB to help prevent this from happening -

• statement_timeout - the maximum time a single statement can run. If it exceeds this time, it will be stopped.
• idle_in_transaction_session_timeout - the maximum time a transaction can be idle before it is stopped.
• transaction_timeout - the maximum time a transaction can run before it is stopped.

These timeouts can be set at the role/user level and the cluster level. They can also be applied when starting a transaction, but I suggest you set them on the role/user or cluster. This way, the database can end a query that is taking too long.

They are very easy to try out. You can find more information on all of them here - https://www.postgresql.org/docs/current/runtime-config-client.html.

For the purposes of this post, I am going to set them for the role bryan, which I am also logged in with.

Timeouts

These timeouts work together in some cases.

If you are not using an explicit transaction, only the statement_timeout applies.

But in an explicit transaction, all three may apply.

For example, in a transaction, if you run a single statement that takes longer than the statement_timeout, it will be stopped even if the total transaction time is less than the transaction_timeout.

A transaction that is idle for longer than the idle_in_transaction_session_timeout will be stopped even if the total transaction time is less than the transaction_timeout.

statement_timeout

To set the statement_timeout for the role bryan to 1 second, run -

ALTER ROLE bryan SET statement_timeout = 1000;

You can check the setting with this command (you may need to disconnect and reconnect to see the change) -

SHOW statement_timeout;

Try running a long statement, such as -

SELECT pg_sleep(2)

It will timeout with an error [57014] ERROR: query execution canceled due to statement timeout.

Then run -

SELECT pg_sleep(0.5)

It will complete successfully.

That’s it, no single statement can run longer than 1 second. This does not affect the length of time a transaction can run for, only the individual statements within a transaction.

idle_in_transaction_session_timeout

To set the idle_in_transaction_session_timeout for the role bryan to 5 seconds, run -

ALTER ROLE bryan SET idle_in_transaction_session_timeout = 5000;

You can check the setting with this command (you may need to disconnect and reconnect to see the change) -

SHOW idle_in_transaction_session_timeout;

To try it out, start a transaction -

BEGIN;
SELECT 1;
-- wait more than 5 seconds here
SELECT 2;
-- you should get an error on this statement

transaction_timeout

To set the transaction_timeout for the role bryan to 10 seconds, run -

ALTER ROLE bryan SET transaction_timeout = 10000;

You can check the setting with this command (you may need to disconnect and reconnect to see the change) -

SHOW transaction_timeout;

To try it out, start a transaction -

BEGIN;
SELECT 1;
-- wait a second or two
SELECT 1;
-- keep repeating until you exceed 10 seconds total
-- you should get an error on one of these statements

The error will be -

ERROR: query execution canceled due to transaction timeout 
SQLSTATE: 57014