I've always used PgBouncer in session mode, but its performance isn't so good, as PHP scripts that run for a long time end up with a long session connected to the database (even for minutes), without releasing the connection for the other requests. Increased the number of connections in IDLE until reaching the limit of connections.
I tried switching to Transaction mode, but for lack of knowledge, it turned out to be a bad idea.
As I found out the hard way, some PostgreSQL functions require to be in the same session as the previous command, such as lastval(), currval() after an insert.
Reading the PgBouncer documentation there is a list of functions that are not accepted in transaction mode, but I haven't found any documentation (even PostgreSQL) that says all functions that must run in the same session after an operation (insert, delete, update, etc. ).
Is there any location with all these restrictions documented?
Note: As I use the CodeIgniter framework for PHP, the insert command is executed separately from lastval(), currval(). That's why I can't execute both in the same transaction with the database.