What is alternative to "lock_timeout" in PostgreSQL?

Question

I need to kill my session, if I'm blocking other sessions.But as of now we have only option to kill own session blocked by other sessions using lock_timeout. Do we have option in postgres to timeout own session, if our session blocked other sessions not own session blocked by others?

Unfortunately, there is no such option. Is the issue that you know you will hold a lock for a long time, you just don't know if anyone else will want it? Or that you don't know how long you will hold the lock for? — jjanes, Sep 09 '22 at 15:37

score 0 · Answer 1 · answered Sep 09 '22 at 10:54

0

You should set idle_in_transaction_session_timeout and statement_timeout. Then your blocking session gets killed if it runs too long statements and if it hangs idle in a database transaction.

answered Sep 09 '22 at 10:54

Laurenz Albe

209,280
17
206
263

score 0 · Answer 2 · answered Jan 06 '23 at 16:28

statement_timeout is alternative = lock_timeout in PostgreSQL:

Abort any statement that takes more than the specified amount of time.

For example, SELECT pg_sleep(30); is timed out after 10 seconds as shown below:

postgres=# SET statement_timeout to 10000;
SET
postgres=# SELECT pg_sleep(30);
ERROR:  canceling statement due to statement timeout

What is alternative to "lock_timeout" in PostgreSQL?

2 Answers2