Currently, session poolers operating in transaction mode need to send a "server_reset_query" which is mostly DISCARD ALL.
It seems strange to me that we put this work onto the pooler, forcing poolers to repeatedly issue the same command, at some cost in performance. Measuring the overhead with pgbench might miss the points that poolers are frequently configured on different network hosts and that monitoring tools used in production will record the DISCARD statement. YMMV, but the overhead is measurably non-zero. Proposal is to have a simple new parameter: transaction_cleanup = off (default) | on A setting of "on" will issue the equivalent of a DISCARD ALL as soon as the transaction has been ended by a COMMIT, ROLLBACK or PREPARE. Poolers such as pgbouncer would then be able to connect transaction mode pools by setting transaction_cleanup=on at time of connection, avoiding any need to issue a server_reset_query, removing the DISCARD ALL command from the normal execution path, while still achieving the same thing. This has an additional side benefit: if we know we will clean up at the end of the transaction, then all temp tables become effectively ON COMMIT DROP and we are able to allow temp tables in prepared transactions. There are likely other side benefits from this knowledge, allowing us to further tune the PostgreSQL server to the common use case of transaction session poolers. I think it should be possible to avoid looking for holdable portals if we are dropping them all anyway. Patch attached, passes make check with new tests added. Comments welcome. -- Simon Riggs http://www.EnterpriseDB.com/
transaction_cleanup.v4.patch
Description: Binary data