Session Artifacts: session settings or objects that interfere with Supavisor/PgBouncer

[TheOtherBrian1]

When using connection poolers like PgBouncer or Supavisor, connections can be reassigned between clients. That means settings and objects created for one client may end up being present for others. This may result in three distinct errors.

Prepared statement errors:

Some external Postgres libraries, such as Prisma and Drizzle, cache query plans as named prepared statements to avoid overhead from re-parsing and planning queries repeatedly.

However, these prepared statements only persist within the database connection in which they were defined.

As connections are reassigned by Supavisor, it can result in the errors:

• prepared_statement "some_name" does not exist
• prepared_statement "some_name" already exists

Solutions

For advice on how to disable them, check out the below troubleshooting guide

• https://github.com/orgs/supabase/discussions/28239#discussion-6985930

Cursor/Portal errors:

Cursors/Portals act like symbolic bookmarks for result sets, allowing partial retrieval of large queries:

• Fetch the first 1,000 rows
• Then the next 1,000
• And so on, without issuing multiple queries

They are one of many ways Postgres facilitates pagination(https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/).

Some external ORMs, predominantly Django, may try to create these objects if not explicitly disabled.

Like prepared statements, cursors and portals only exist within a single connection.

When PgBouncer and Supavisor shuffle connections between clients, it can result in the errors:

• cursor/portal "some_name" does not exist
• cursor/portal "some_name" already exists

Important caveats:

• Unlike prepared statements, which some poolers can use, cursors/portals are incompatible with all transactional serverside poolers.
• Some client libraries, such as SQLAlchemy, use the term cursor to refer to their query orchestrator. Despite sharing a name, they are not the same as Postgres cursor objects and are fine to use.

Solutions:

• Stop using cursors/portals (disable the setting in your ORM). You can use an alternative pagination strategy.
• Or only use them with the direct/session mode strings (port 5432)

Session-Level settings and characteristics errors

You can change Postgres settings within the context of a single connection:

SET statement_timeout TO '1s';

-- alternatively, you could use the format
 --SELECT set_config('statement_timeout', '1s', false),

Or set session characteristics that affect all subsequent transactions:

SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

When a pooler reuses or reassigns connections, these session settings persist and can cause unpredictable behavior for other clients.

For instance, the Postgres interface DBeaver when set to read-only mode, will change a connections settings. When the connection is swapped by the serverside pooler, this will cause other clients to experience unexpected read-only errors.

Solutions

1. **Don’t create session variables with a transaction pooler (port 6543) **
2. Use the Direct connection / session mode stings:

If you must create session variables, it is better to avoid shared poolers and instead use Supavisor in session mode or direct connections (port 5432), which do not share sessions between clients.

3. Global/role settings: change settings at the role or global level

If you want a setting to be permanent (not just for a connection's lifetime), you should consider changing the setting at the role level instead:

ALTER ROLE <role_name> SET <setting_name> TO <some_val>;

4. Local (transaction-level) settings: scope changes to a single transaction only:

It's possible to change a setting just for the duration of a transaction. For this to work, the query must be wrapped in a BEGIN/COMMIT block:

BEGIN;

SET LOCAL <your_setting> TO <some_val>;

-- alternatively, you can change the setting locally with the set_config function
-- SELECT set_config('<your_setting>', '<some_val>', TRUE); 

**YOUR_QUERY_HERE**;

COMMIT;