1

I have a PostgreSQL server with pgBouncer as connection pooler.

My application is running on Elixir.

This is my config file for pgBouncer:

* = host=X.X.X.X  port=5432
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
admin_users = admin
pool_mode = transaction
ignore_startup_parameters = extra_float_digits
server_check_query = select 1
server_check_delay = 30
max_client_conn = 10000
default_pool_size = 5
min_pool_size = 3
reserve_pool_size = 3
server_reset_query = DEALLOCATE ALL;

When I use the transaction pooling I'm getting this error:

ERROR 08P01 (protocol_violation) bind message supplies 4 parameters, but prepared statement "ecto_323" requires 2

Then I changed it to session pooling mode. This time I'm getting this error:

ERROR 26000 (invalid_sql_statement_name) prepared statement "ecto_83" does not exist

How do I fix this from pgBouncer?

When I connect the DB directly, I didn't see any errors. It was running more than a year without any proxy. We are implementing pgBouncer now.

Improve this question
1
  • Did you restart the app server and pgbouncer after changing the pooling mode?
    – jjanes
    Aug 25, 2020 at 16:44

2 Answers 2

Reset to default
4

The error in session pooling mode indicates that you are doing something different that is not connected with pgBouncer: if the application has the same session all the time, the only explanation for a missing prepared statement is that it either was never declared or deallocated. That should happen just the same without pgBouncer.

The maxim here is that everything should work with session pooling mode.

The error with transaction pooling mode indicates that you are using the same prepared statement name for different statements. Now when an application thread gets some other thread' session in the next transaction, and it gets the prepared statements along with it, that is no surprise.

Improve this answer
2

With transaction pooling, you should disable prepared statements in the application, says the faq

Improve this answer
6
  • Which is stupid advice from PGBouncer. In almost any sane security practice, prepared statements are mandatory. The correct advice is "Find a better alternative to PGBouncer" :(
    – Shayne
    Mar 16 at 8:52
  • @Shayne Totally understand that reaction! I felt the same way when I first read that I needed to disable prepared statements, which sounded ridiculous and dangerous. Turns out PgBouncer just can't support named prepared statements, but has no problem with unnamed prepared statements, which any good postgres client library will use over the wire when communicating with Postgres. So it still is a prepared statement, it just doesn't support the up-front prepared, named version of it - you can learn more here jpcamara.com/2023/04/12/…
    – jpcamara
    Apr 13 at 19:29
  • @Shayne it only means that you cannot use pool_mode=transaction but must use pool_mode=session if you want to use prepared transactions. This is caused by the internal implementation of pool_mode=transaction in PgBouncer.
    – Mikko Rantalainen
    Jun 20 at 9:23
  • Ok that makes a bit more sense. Might be worth editing that into the answer to make it clearer.
    – Shayne
    Jul 4 at 9:20
  • 1
    @MikkoRantalainen you can still use transaction mode. You just can't use named prepared statements. From a security perspective, you are still free to use prepared statements to sanitize, you just cannot cache them.
    – jpcamara
    Jul 5 at 13:15

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.