2

I'm using pgbouncer on a web app and most threads begin with a BEGIN and end with a COMMIT or a ROLLBACK, so we're using transaction pooling and everything is fine.

However, we also have some processes which don't use transactions: instead, they just issue commands one after another.

I believe that, under transaction pooling, every command is a transaction by itself, just the way it is when you're connected directly to the server, and perhaps every command is getting a different connection from the pool. But I've been told that pgbouncer wouldn't do that and instead would never find the final COMMIT/ROLLBACK and thus the connection wouldn't return to the pool.

Anybody knows what happens? I couldn't find anything in the documentation.

rat
  • 487
  • 1
  • 5
  • 10
  • 2
    What happened when you tried it? In theory it should work, but clients do all kinds of weird things to what they send over the wire. – jjanes Apr 30 '14 at 17:42
  • References? Been told by whom, where? Links? I'd like to see what was written if possible, as that sounds really weird and worth chasing up. – Craig Ringer May 01 '14 at 00:54
  • It works. I haven't had time to really test and it either works like session- or autocommit-mode. I think it's the later, but a friend says its the former. We're both just guessing. I'll let you know if/when I find out for sure, but I don't have the time for it this week :( – rat May 01 '14 at 07:19
  • Hey rat, did you find that out? – Vadim Samokhin Sep 13 '22 at 19:31
  • We had lockups, so we just used a second connection with AutoCommit enabled explicitly (perl dbi). We feared that that wouldn’t be stable, but it has been pretty solid for 8 years now. So… no, I didn’t find out, sorry. – rat Sep 14 '22 at 22:31

1 Answers1

3

https://pgbouncer.github.io/usage.html

https://pgbouncer.github.io/config.html#description

Transaction pooling

A server connection is assigned to client only during a transaction. When PgBouncer notices that transaction is over, the server connection will be put back into the pool.

In your case if transaction is never ended (commited, rolled back) it will hit idle_transaction_timeout (default disabled) and idle in transaction connection will go back to pool, allowing others to connect. If you have the default value for it, at some point all connection pool will be filled, so new will be rejected. From this point you single statements won't work - they will wait for free connection that never appears.

Regarding single statements - they are not "transformed to transactions by pgbounce" neither "in transaction pooling, every command is a transaction by itself". This is controlled by AUTOCOMMIT for each session.

Community
  • 1
  • 1
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • So if autocommit is on and there are no explicit transactions, every statement is implicitly wrapped into transaction, so when transaction mode is used, a statement gets a connection from pgbouncer each time it fires, and when a statement finishes its work, connection is returned to pgbouncer. Correct? – Vadim Samokhin Sep 13 '22 at 19:47
  • I wanted to mix them up, and couldn’t find a reliable way. So I’m using a second connection, with AutoCommit enabled. That did work. – rat Sep 14 '22 at 22:34