[LoSboccacc]

> Advanced transactional pooling > Odyssey tracks current transaction state and in case of unexpected client disconnection can emit automatic Cancel connection and do Rollback

that's my biggest issue with pgbouncer, is there a docker image for it?

[polthrowaway]

is this a problem with pg bouncer? pg bouncer supports transactional pooling which is meant to only return connections back into the pool when they are not in a transaction. so seeing as it keeps tracking of the transaction status it seems pretty crazy that it would put a connection back into the pool that has an open transaction.

i tested this on my local machine and if i drop a connection while it is inside a transaction it closes the server connection.

client close:

    2018-05-30 15:28:42.068 21702 LOG C-0x7f9728816a10: DB/USER@[::1]:64342 closing because: client close request (age=85)
    2018-05-30 15:28:42.068 21702 LOG S-0x7f972980a190: DB/USER@127.0.0.1:5432 closing because: unclean server (age=85)

client unexpected death:

    2018-05-30 15:33:57.197 21702 LOG C-0x7f9728816a10: DB/USER@[::1]:64376 closing because: client unexpected eof (age=15)
    2018-05-30 15:33:57.198 21702 LOG S-0x7f972980a190: DB/USER@127.0.0.1:5432 closing because: unclean server (age=10)

i guess it sucks that it doesn't reuse the connection but presumably this shouldn't happen that often that it would actually be a problem.

[cpburns2009]

Do you know how pgbouncer handles an unexpected client disconnect when using session pooling? Using pgbouncer has been on my to-do list for a while and the comment by LoSboccacc gives me some concern though it's vague.

[LoSboccacc]

I’m unsure on the specifics and it might very well be an interaction between jdbc or something, what I know is that if I change the connection reuse settings from session to transaction I get leakage and eventual exhaustion, so currently I’ve a pgbouncer in session mode on every node and connect each to the backend. That uses a little more connection on the server but for now is not critical so I haven’t investigated in deep and if easier I’d just hop to an equivalent because we’re really short on hands right now.

[foobarbazetc]

You need to make sure you turn off prepared statements if you want to use pgbouncer in transaction mode with pgjdbc.

FWIW we use it in exactly that way to serve many thousands of rps with no issues.

[pbarnes_1]

But... if you never commit the transaction in pgbouncer you don't need to roll it back. It'll just auto-rollback on server connection drop.