How to use supavisor with asyncpg?

[awalias]

Discussed in https://github.com/orgs/supabase/discussions/20775

^{Originally posted by **nsbradford** January 26, 2024} For reference: - https://github.com/MagicStack/asyncpg/issues/1058 - https://github.com/MagicStack/asyncpg/issues/837 - https://github.com/sqlalchemy/sqlalchemy/issues/6467 TLDR: [asyncpg](https://github.com/MagicStack/asyncpg) is go-to Postgres client, but it has incompatibility with pgbouncer because of issues with prepared statements. Supabase uses it's own in-house [Supavisor](https://supabase.com/blog/supavisor-1-million), but still seems to have the same issue. There are workarounds if you have access to the pgbouncer instance, but there doesn't seem to be a way to configure Supavisor easily? Are there any other workarounds for this? Workaround details: https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#prepared-statement-name > To prevent a buildup of useless prepared statements in your application, it’s important to use the [NullPool](https://docs.sqlalchemy.org/en/20/core/pooling.html#sqlalchemy.pool.NullPool) pool class, and to configure PgBouncer to use [DISCARD](https://www.postgresql.org/docs/current/sql-discard.html) when returning connections. The DISCARD command is used to release resources held by the db connection, including prepared statements. Without proper setup, prepared statements can accumulate quickly and cause performance issues.

[nsbradford]

Would love a solution/guidance here 🙏 so far the likely option is moving to Session mode, though given our Supabase Max client connections is fixed i would be pretty concerned about exhausting allowed connections

[StacySiz]

Any updates on this? Besides moving to sync Session mode?

[gregm711]

Same, would love this functionality as well - Any updates?

[nileshtrivedi]

I ran into this too. I have IPv6 at home, so I can switch to port 5432 and bypass it, but at work where I don't have IPv6, I'm forced to use Supavisor which leads to this error:

An error occurred: (sqlalchemy.dialects.postgresql.asyncpg.Error) <class 'asyncpg.exceptions.InvalidSQLStatementNameError'>: prepared statement "asyncpg_stmt_7" does not exist HINT:
NOTE: pgbouncer with pool_mode set to "transaction" or "statement" does not support prepared statements properly. You have two options:

• if you are using pgbouncer for connection pooling to a single server, switch to the connection pool functionality provided by asyncpg, it is a much better option for this purpose;

• if you have no option of avoiding the use of pgbouncer, then you can set statement_cache_size to 0 when creating the asyncpg connection object.

[SQL: SELECT * FROM public.users WHERE identifier = $1] [parameters: ('admin',)] (Background on this error at: https://sqlalche.me/e/20/dbapi)