CODEWITHSUNDEEP
postgresqlpgbouncer
mscrivo
mscrivo

Reputation: 1127

How to use statement_timeout with pgBouncer in transaction mode

Using pgBouncer in transaction mode seems to be the optimal way of reducing the number of active connection at any given time, but it's also given rise to a problem that I'm not sure how to solve effectively.

Say you want to have a blanket statement_timeout of 5 mins at the DB level just for the safety of preventing any unexpected super long running queries, but have a bunch of workers that run statements that normally take much longer than that. Without pgBouncer, you could just temporarily set the statement_timeout on the session and set it back to default afterwords and that all works fine. However, with pgBouncer in transaction mode this doesn't work reliably without wrapping the offending operations in a transaction and setting a local statement_timeout. However, that is not desirable either because then you have arbitrarily long running transactions just to achieve the desired timeout settings.

Is there any way to apply local timeouts to statements when using pgBouncer in transaction mode, but without having to use a transaction?

Upvotes: 3

Views: 3287

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246013

You could use SET LOCAL to change the parameter only for the current transaction:

BEGIN;
SET LOCAL statement_timeout = '1h';
SELECT /* long running query */;
COMMIT;
-- now statement_timeout is reset to the original value

Upvotes: 5

jjanes
jjanes

Reputation: 44137

pgbouncer transaction pooling mode is mostly a way to paper over poorly designed or implemented applications. I wouldn't say it is "optimal" for anything.

but have a bunch of workers that run statements that normally take much longer than that

Maybe make those workers run through a different pool? Or bypass pgbouncer and hit the database directly.

However, that is not desirable either because then you have arbitrarily long running transactions

Every statement runs inside a transaction. Whether you open and close them explicitly or implicitly, they are still there.

Upvotes: 2

Related Questions