alt Hacker Newswe 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.
Replies
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.
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.