[mmakeev]

we moved our django app behind pgbouncer transaction pooling a few days ago and the surprise wasn't SET so much as queryset.iterator(). it relies on server side cursors, which don't survive being pooled, so we had to disable it everywhere and let it fall back to client side. also had to move statement_timeout out of the app's connection options into the pooler's own connect query, since libpq startup params just get silently ignored behind it.

[HackerThemAll]

Using server-side cursors is a sign of bad design. You use the PostgreSQL server's resources as a cache when you're fetching and processing stuff row by row, which is very bad. Just get the data you requested in the first place in one go and do your stuff in your app. Or process all data on the server and then get the processed data in one batch as well.

[fsuts]

As said above, SQL is a set based language and one shouldn’t write a cursor to deal with individual rows unless no other way.

And if writing a server side cursor, probably better to write a stored procedure /function and put the cursor and its logic in it, and then call that rather than handle in application

[mmakeev]

two different things are getting called "cursor" here. queryset.iterator() isn't server-side row-by-row processing, it's one set-based query streamed to the client in chunks so a big export doesn't materialize in memory all at once, "get it in one go" is exactly the OOM it avoids.

the honest knock on the streaming kind isn't "bad design", it's that it holds a portal open server-side, which is why it can't survive transaction pooling. so the pooler-friendly fix isn't one giant fetch, it's keyset pagination (where id > last limit n), stateless and constant memory, which is what we moved those paths to.

[levkk]

Handling cursors is tough - they are very much session-level objects, so even if we, say, pinned your client while it uses that cursor, which would work, that would decrease the performance of connection pooling overall.

So, what's better, breaking your app initially so you know to remove that feature, or letting it work silently while the connection pool isn't 100% in transaction mode? Tough call.

[rst]

Maybe don't reassign the sesssion to a different client so long as there's a cursor open (the way that most poolers have a mode that won't reassign a session that has a transaction open)?

[Maledictus]

Which one did you pick?

[levkk]

Pin the client temporarily. I'm assuming that our users know what they are doing. I could be wrong, but so far so good.