Postgres statement_timeout does not work as expected

Question

I am working on a use case where in my current api application I need to kill any query that has been running more than 30 sec (as my server has a timeout of 30 sec but the query keeps running on Postgres).

So after some finding i came across the statement_timeout configuration in postgres. and implemented it in my sqlAlchemy code like this:

@contextmanager
def db_session():
    """Executes the query."""
    import os
    from my_aws import secretsmanager
    secret_name = f'<my_secrey_key>'
    secret = secretsmanager.get_secret(secret_name)
    conn = f'{secret["dbname"]}://{secret["username"]}:{secret["password"]}@' \
           f'{secret["host"]}:{secret["port"]}/{secret["dbname"]}'
    eng = create_engine(
        conn,
        connect_args={'options': '-c statement_timeout=30s'})
    connection = eng.connect()
    db_session = scoped_session(sessionmaker(autocommit=False, autoflush=True, bind=eng))
    yield db_session
    db_session.close()
    connection.close()

So my expectation here was that any query whcih cannot complete within 30s should timeout and return an error.

So when testing this.

I place a lock in one of my tables to delay my queries by doing this:


BEGIN WORK;
LOCK TABLE <schema>.<table_name> IN ACCESS EXCLUSIVE mode;

then i trigger an API call which queries the locked table (from above). this api does not repond as expected becuase the query is unable to execute witin 30 sec.

however the query does not terminate and i can still see it running in the pg_stat_activity

SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' and usename='api_user'
ORDER BY query_start desc;

So the above query gives the reponse:

pid |age            |usename |query                          
----|---------------|--------|-------------------------------
3334|00:05:17.962059|api_user|SELECT count(*) AS count_1 ¶FRO
1752|00:05:22.577919|api_user|COMMIT                         
1754|00:05:22.627446|api_user|COMMIT                         
3270|00:05:22.791417|api_user|SELECT count(*) AS count_1 ¶FRO
1755|00:05:23.058261|api_user|COMMIT                         
1753|00:05:23.123582|api_user|COMMIT                         
1689|00:05:24.149163|api_user|SELECT count(*) AS count_1 ¶FRO
1759|00:05:24.579171|api_user|SELECT DISTINCT sum(public.dema
1760|00:05:24.631371|api_user|SELECT count(*) AS count_1 ¶FRO

As you can see that the query on the locked tables are still waiting from more than 5 min.

Is there something wrong with my understanding of statement_timeout here.

FYI: I can see that the timeout is set on the postgres as the result of this query:

show statement_timeout;

Result:

statement_timeout|
-----------------|
30s              |

Is that SHOW command run from within sqlalchemy? I ask because that ASCII-art looks to me more like a format I would expect from psql than from python. — jjanes
– jjanes, Commented Jun 3, 2021 at 14:29

Laurenz Albe · Accepted Answer · 2021-06-30 14:21:41Z

5

I recommend that you set the parameter in postgresql.conf (then it is valid for the whole PostgreSQL server) or with ALTER DATABASE (then it is valid only for new connections to that database).

If that does not do the trick, the setting must be overridden somewhere. To debug, run the following SQL statement using SQLAlchemy:

SELECT current_setting('statement_timeout');

However, when I look at your query, perhaps everything is working anyway: add the state column to the pg_stat_activity query and check if the state is indeed active. Perhaps the query has already been canceled, and the state is idle or idle in transaction (aborted) (note that query shows the last query on that connection, which need not be active any more).

answered Jun 30, 2021 at 14:21

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ed Mendez · Accepted Answer · 2021-07-01 15:07:30Z

1

I think The statement_timeout should be a value in milliseconds. If you are really passing in 30s, that might be the wrong parameter value. Try using 30000 for 30 seconds.

   eng = create_engine(
        conn,
        connect_args={'options': '-c statement_timeout=30000'})

answered Jul 1, 2021 at 15:07

Ed Mendez

1,77312 silver badges17 bronze badges

Collectives™ on Stack Overflow

Postgres statement_timeout does not work as expected

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related