Connecting through pgbouncer raises duplicate_prepared_statement error

[mmmries]

I'm experimenting with elixir in an existing production system and want to connect to my postgres server through a pgbouncer instance to reduce the total number of connections to the actual database.

When I try to start my application I get the following error:

13:53:28.287 [error] Postgrex.Protocol (#PID<0.225.0>) failed to connect: ** (Postgrex.Error) ERROR 42P05 (duplicate_prepared_statement): prepared statement "POSTGREX_BEGIN" already exists

I think this is because postgrex is trying to create a prepared statement in the context of each connection, but the connection is actually being shared at the pgbouncer level. Has anyone tried to do this before? Is there a configuration option for disabling those prepared statements or a way to check if they exist before creating them?

[michalmuskala]

PgBouncer requires setting the "unnamed" prepare mode. Do you see this despite the setting?

https://github.com/elixir-ecto/postgrex#pgbouncer

PgBouncer

When using PgBouncer with transaction or statement pooling named prepared queries can not be used because the bouncer may route requests from the same postgrex connection to different PostgreSQL backend processes and discards named queries after the transactions closes. To force unnamed prepared queries:

Postgrex.start_link(prepare: :unnamed)

[mmmries]

That's perfect. I was trying to search for an answer to this problem by searching for something from the error message rather than searching for pgbouner. Should have read the whole README before posting 😬

Thanks for the quick answer. I was able to get connections opened now 👍 💛 💙 💚 ❤️

[jeroenhouben]

To force unnamed prepared queries

@michalmuskala I assume there is a downside to this? This postgresq docs say:

An arbitrary name given to this particular prepared statement. It must be unique within a single session and is subsequently used to execute or deallocate a previously prepared statement.

[michalmuskala]

Unnamed prepared statements are a feature of the wire protocol and not available through regular SQL. My understanding is that the downside is that they imply two round-trips for each query - one to prepare and one to execute.