Postgresql – Prepared statements with pgBouncer

pgbouncerpostgresqlprepared-statement

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.

Best Answer

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.

Related Question