pgbouncer prepared statement error

[appflowy]

I encountered the ERROR: prepared statement "s3" already exists error while using transaction mode in Postgres, even when each query was wrapped in a transaction.

I am considering whether adding ?pgbouncer=true to the connection string could solve the prepared statement error (refer to: https://github.com/prisma/prisma/issues/11643).

Here is the connection URL: postgres://postgres:[YOUR-PASSWORD]@db.[DB-KEY].supabase.co:6543/postgres?pgbouncer=true

I also reviewed the related issues: https://github.com/sfackler/rust-postgres/issues/959 https://github.com/sfackler/rust-postgres/issues/646

[sfackler]

pgbouncer=true does nothing in this library.

[appflowy]

Thus, the only way to bypass the prepared statement error when using Postgres with transaction mode is by using simple_query. I tried using query_raw, but it didn't work. Any suggestions to fix this kind of errors?

[sfackler]

If you're running pgbouncer in transaction mode, I think you shouldn't have any problems as long as you make sure prepared statements don't outlive the transaction they were created in.

[appflowy]

If we're using the transaction mode of pgbouncer, each query needs to be wrapped in a transaction returned by client.transaction(). Additionally, it's necessary to clean up the prepared statement at the end of the transaction by calling transaction.statement_cache().clear(). I'm also using Deadpool, and I'm not sure if that might cause the "prepared statement already exists" error.

Here's an example:

let transaction = client.transaction().await?;
transaction.query_raw(xxx);
transaction.statement_cache().clear();
transaction.commit().await?;

Please correct me if I'm wrong.

[sfackler]

statement_cache is a deadpool thing - I can't help you there.