Diesel Error: prepared statement "__diesel_stmt_0" already exists or unnamed prepared statement does not exist

[davidxhive]

Hi all, I am using r2d2 postgres connection pool and diesel. I am running into a situation where one function that uses sql_query() randomly gives me an error like below
[2023-03-30T01:35:16Z ERROR r2d2] unnamed prepared statement does not exist
Or
[2023-03-30T01:35:16Z ERROR r2d2] prepared statement "__diesel_stmt_0" already exists

I have a feeling it's related to sql_query since this is the only place in my entire application where I have used it, and all other functions use diesel's ORM approach.

Okay just kidding, even ORM approach has "prepared statement "__diesel_stmt_3" already exists" issues.

Now it seems like all my endpoints are having issue executing diesel sql code. Everything has this prepared statement already exists issue.

Is this because I am using a r2d2 connection pool and the prepared statements are repeated/not repeated on the same connection or something?

Any thoughts on what could the be the cause of this? I have no clue where to even begin investigating.

[weiznich]

This error message indicates that your database system does not support named prepared statements. That either means that you do not use postgres as backend, but something else that speaks postgres wire protocol. In that case that's an important missing feature in their implementation and should be fixed there.
The other variant is that you use an additional extern connection pooling layer like pgbouncer that also don't support prepared statements in some configurations. You cannot use that in combination with diesels build-in PgConnection as the usage of prepared statements is considered to be a fundamental security feature for diesel.

[davidxhive]

Thank you! Yes there's a PgBouncer and removing it worked!

[evforde]

@weiznich Thanks for your guidance as always. Are you able to elaborate on:

as the usage of prepared statements is considered to be a fundamental security feature for diesel.

I haven't seen prepared statements used for postgres in other ORMs. I've also noticed that prepared statements can break when one postgres client issues certain table altering commands, something like cached plan must not change result type

So, I'm thinking about disabling prepared statements in my application whose query patterns/connection reuse practices likely don't have much of a performance benefit from reusing query plans. Very curious to learn more about their use :)

[weiznich]

As written above: Using prepared statements is a fundamental security feature of diesel. It's not "just" an optimization for performance, but it its the best way to prevent SQL injections. See here for details. Therefore disabling prepared statements is not supported by any of the built-in connection types.

[dhbradshaw]

FYI, it looks like pgbouncer's support for prepared statements in transactions has improved:

https://www.crunchydata.com/blog/prepared-statements-in-transaction-mode-for-pgbouncer

Whether or not that helps for diesel applications seems to depend on whether diesel uses prepared statements at the protocol level or not:

Caveat one: only protocol level things
The first caveat is that this only applies to protocol-level prepared statements. For most languages, this means that we are using the libpq function PQprepare rather than issuing a SQL command such as PREPARE foo AS ... This is an important distinction as the former sends specific protocol-level commands to Postgres that PgBouncer can intercept and act upon, while the latter simply sends a string of text over the wire, which PgBouncer knows nothing about.

[weiznich]

That's great news. Thanks for pointing out that this feature is now implemented on their site. Diesel uses the libpq function for creating a prepared statement so it should work fine with their implementation:

diesel/diesel/src/pg/connection/raw.rs

Line 102 in ac43e5b

let ptr = PQprepare(