Does anyone know if statement_timeout works with pgBouncer in transaction pooling mode?
🇳🇱
@Gargron transactions and statements/queries are not exactly the same thing. One transaction can have many statements, surely.
@cms I mean, it'll definitely work if you prepend "set statement_timeout = 5s;" to every query that gets sent from rails to pgbouncer. But with rails configuration? I think it executes that once per each connection in its pool... Wherein those connections to pgbouncer are not necessarily the real connections to postgres...
@Gargron aha, you have rails pooling in front of pgbouncer pooling. Is that useful?
@cms It's necessary if you want all your threads to have a database connection. I mean it's kind of what pgbouncer is all about, isn't it? Allowing a much higher connection count than postgres itself
@Gargron right, sorry it's been a while since I set up rails for this. Yes, that's what pgbouncer is about. Yes, I think if you're threaded you need to have rails pool as big as thread number
@cms Anyway, I think I would need to monkey-patch ActiveRecord to prepend the statement_timeout setting to every query
@Gargron yeah, wondering if there's anything you can do to set it per user , either server side or at pg_bouncer.
@cms Per-user it would also interfere with sidekiq queries which do not have such a clear maximum. Plus I kind of need solutions that will work for all deployments out of the box, rather than requiring manual admin intervention, whenever possible...
@Gargron Do you have a link to the offending code? No promises but I can take a look. I can't think of any solutions from the description alone. But I am happy to look over the code to see if anything sticks out to me.
@freemo Well, I'm occassionaly experiencing deadlock situations when all queries go into idle mode. Specifically in the puma workers, there is 0 reason for any query to ever take longer than 5s to execute, on any environment. So adding a timeout there would let the problem fix itself. However, I am not sure it works with transaction pooling, because a session doesn't really exist with transaction pooling. Every query you execute, might get a different underlying connection to postgres.