0

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.

Improve this question

1 Answer 1

Reset to default
0

I don't know of a comprehensive list.

Other functions that won't work are the ones that manage session level advisory locks, but also set_config and pg_listening_channels, and in general everything that affects the session state.

Improve this answer
2
  • Reading the framework documentation I saw that there is a command to initiate and complete a transaction. With this command I realized that requests executed after starting the transaction and before ending the transaction are executed in PostgreSQL with the same backend_xid. Does this mean the command is using the same session or is it within the same transaction? In other words, can I change PgBoucner's pool to work with transactions?
    – Tom
    Nov 5, 2021 at 20:27
  • 1
    backend_xid identifies the database transaction, so that looks good for transaction mode pooling. Test and see if it works!
    – Laurenz Albe
    Nov 5, 2021 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.