Postgrest Server Does'nt respond for DB error SQL Error [57014]: ERROR: canceling statement due to statement timeout .

[anand-padmanabhan]

Environment

• PostgreSQL version: postgis/postgis:15-3.3-alpine
• PostgREST version :postgrest/postgrest:v10.1.1
• Operating system: Ubuntu 20.04.5 LTS

If postgrest is trying to update a row which has an active DB ExclusiveLock . Postgrest doesnt reponds with any error/timeout.
Basically postgrest is not handling SQL Error [57014]: ERROR: canceling statement due to statement timeout error.

Steps to reproduce
1. Acquire a lock on row
import time
import psycopg2
conn = psycopg2.connect("dbname=abc user=abc password=abc host=172.17.0.1")
cur = conn.cursor()
cur.execute("BEGIN;")
cur.execute("SELECT id FROM abc.abc WHERE id='A104' FOR UPDATE;")
time.sleep(3000)
cur.close()

2.Confirm Lock is acquired
SELECT
locktype,
relation::regclass, -- Converts relation OID to a name for table-level locks
mode,
transactionid AS txn_id,
virtualtransaction AS vtxn_id,
pid,
granted
FROM pg_locks
LEFT JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE
-- Optionally filter for specific types of locks or tables
pg_class.relname = 'abc' and
true;

3.Update request from Postgrest
curl -X PATCH
-H "Content-Type: application/json"
-d '{"column_1": "1000"}'
--verbose
"http://172.17.0.1:3015/abc?id=eq.A104"

**This request doesnt give any response..it running indefinitely.

4.Update request through sql query
update abc.abc set column_1='100'
where id='A101';

Results in below error:
SQL Error [57014]: ERROR: canceling statement due to statement timeout

[steve-chavez]

statement_timeout is 0 by default, i.e. it's disabled. https://postgresqlco.nf/doc/en/param/statement_timeout/

user=abc

Likely your abc user has a non 0 statement_timeout that's why you get the error. with your python client

Once you connect to PostgREST with a user that has statement_timeout set, you should get the same error.

Note: PostgREST has first class support for statement_timeout: https://postgrest.org/en/v12/references/transactions.html#impersonated-role-settings