Named prepared statements not working

[marcustut]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Rust's sqlx requires connection pooler to support named prepared statements in order to work properly, it has always been an issue up until the release of pgcat, that finally people can use it with a proper connection pooler, since #69 is closed I assume the latest release v1.0.0 for supavisor has been deployed for all Supabase users, however I did a test and sqlx still doesn't work and is due to named prepared statements.

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

Clone the supavisor-sqlx and replace the DATABASE_URL in .env, you should see this error.

Expected behavior

Expect named prepared statements to work with sqlx

[chasers]

Got it thanks! We will fix!

[rbox-risk]

I think possibly related, but I too am using rust but with tokio_postgres. If I use existing direct connection to sbase everything works. As soon as I switch to connecting to the new connection pool via url to execute against a supabase function I get Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E26000), message: "prepared statement \"s1\" does not exist", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("prepare.c"), line: Some(451), routine: Some("FetchPreparedStatement") }) }

The error shows in both cases when I use let stmt = client.prepare("...").await?; then client.query(&stmt,&params) and when I bypass the prepare and straight to client.query. Left a comment as my code is part of a bigger thing and creating a repo might take a while - hence the comment. Thanks

[Dkendal]

We're using Ecto with elixir and running into the exact same issue where the client is complaining the the number of parameters submitted differed from what was required.

[salomartin]

This is a critical fix before you switch to Supavisor. Same issue when connecting from Kotlin based Spring apps.

[marektomczyk]

Same for Postgraphile.

[faces-of-eth]

Same thing here with Ent.go and pgx

INITIALIZING DB PROVIDER
RUNNING MIGRATIONS
{"severity":"fatal","error":"querying server version ERROR: prepared statement "stmtcache_63d758aee2eca028cbaff973427e2801cd4bb8e4ee2e9f9f" already exists (SQLSTATE 42P05)","timestamp":"2024-01-16T21:32:32.621157-08:00","message":"FATAL runMigration"}
exit status 1

[danwritecode]

+1 on this issue with sqlx with the new connection pooler db url

[brycegoh]

any updates on this?

[nk9]

I am also using sqlx and getting the above error. Has anyone identified a workaround, besides switching from query!() to sqlx::query()? As it stands, I can use the macro fine locally, but as soon as I change my DATABASE_URL to the remote (production) Supabase connection string, I can no longer build and run my program. This sqlx issue makes clear that it's not possible to use the query!() macro without support for prepared statements from the database.

[nk9]

I actually misunderstood the issue here. The Postgres driver for sqlx requires named prepared statements for nearly all queries, whether they are executed with the query!() macro or directly via sqlx::query(). This happens even if you use .persistent(false), for some reason.

To be clear, this means that right now you CANNOT use sqlx with hosted Supabase. One of the two projects will need to make a change to support the other. (See below for workaround.)

I have all this code I've written with my local database which I now can't use to push data to my production Supabase instance. ☹️ Is there an ETA on Supabase supporting named prepared statements?

[nk9]

Quick update from @chasers via Twitter:

Taking longer to get back to this but we’re close.

All libs let you turn off prepared statements though.

And if you need them for something you can use session mode on port 5432 with your pooler url.

And indeed, if you switch from port 6543 to port 5432, the named prepared statements will work and sqlx will happily use a hosted Supabase instance. Thanks very much for the workaround!

[BennettDixon]

While switching to session mode does function as a workaround, it introduces challenges in scaling a micro-service or serverless based application. Given that pgbouncer added support for named statements in transaction mode in version 1.21.0, is there a roadmap or discussion regarding adding similar support in Supavisor?

[JelteF]

Okay, the reason this does not work, even though supavisor advertises prepared statement support is that it only supports SQL level prepared statements (PREPARE & EXECUTE SQL commands), and not protocol level prepared statements (Parse, Describe, Bind, Execute protocol messages). Which honestly is quite interesting as all of the other connection poolers that support prepared statement (PgBouncer, Odyssey and pgcat) only support protocol level prepared statements.

Every client I know of uses protocol level prepared statements, not SQL level prepared statements. So it's expected that transaction pooling doesn't work if your client uses prepared statements. The only time when SQL level prepared statements are used is usually in interactive applications.