2

I can set the global statement_timeout parameter on my PostgreSQL (14+) database to automatically kill long running queries.

However, the clients can override it and set their own statement_timeout that is significantly longer than the server one.

What I'd like to do is let clients set their statement_timeout up to the global limit, but if they go over it - either complain when they try to set a larger value, or just set max(global_timeout, session_timeout)

I don't think this is currently possible in postgresql, but I could be missing something.

--

My reasoning here is that I never want something hitting the high performance OLTP database that is going to bog resources for terribly long. However we have client software that is automatically setting really long statement_timeouts and overriding the global settings, and then abusing the server - running reports which take many hours to process, that should be run on the backend ERP database, for example.

I'd like to enforce: "If your query really needs to take more than 10 minutes to run - you should be running it somewhere else".

--

If we can't do this in pg directly, could we do it in pgbouncer, or pgpool, or rds proxy?

1
  • 1
    In pgbouncer, you can use ignore_startup_parameters, but that only works if the parameter is set in the startup packet, which I doubt is what your bad client does. If your client software misbehaves, maybe you should just ban it from the connecting to that system altogether.
    – jjanes
    Commented Aug 2, 2023 at 16:37

1 Answer 1

2

As far as I can tell, the only way would be to modify the PostgreSQL source.

You could regularly run a statement that cancels long running statements:

SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state = 'active'
  AND state_change < clock_timestamp() - INTERVAL '10 minutes';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.