Resolving 'cannot execute UPDATE in a read-only transaction' on transaction pooler connections

Key technical terms#

Transaction Pooling A connection management method used by tools like Supavisor or PgBouncer. Instead of giving every client a dedicated, permanent connection to the database, the pooler maintains a small set of "backend connections." It lends one of these connections to a client for the duration of a single transaction, then immediately takes it back to give to another client.

Backend Connection The actual physical process on the Postgres server that executes your queries. In pooling environments, one backend connection will serve many different database clients over its lifetime.

Session-Level State That backend connection has state. Postgres connections carry settings like timezone, memory limits, search path, read-only mode etc. In a normal setup where each client has its own connection, this doesn't matter. When the client disconnects, the connection and all its state go away. In a pooled environment, the connection doesn't go away. It goes back into the pool, settings and all, and the next client who gets it inherits whatever was left behind.

---

Understanding the problem: The "sticky" state#

When you encounter the error cannot execute UPDATE in a read-only transaction while using a transaction pooler (typically on port 6543), even when you have verified that the connection is made to the primary database and the database itself is not in read-only mode (check with SHOW default_transaction_read_only; or SELECT pg_is_in_recovery(); using a direct connection on port 5432), it signifies that a backend connection has been unintentionally locked into a read-only state.

Note: If your database is in read-only mode (for example, due to exceeding disk space limits), you can review this guide: Database Size and Read-Only Mode. The remainder of this guide addresses a different issue specific to transaction pooling.

The cause: Connection contamination#

In transaction pooling mode, reset behavior is intentionally limited for performance, so session state can persist unless explicitly reset. If a client, script, or automated task changes a session-level setting, that setting "sticks" to the backend connection.

When that backend connection is returned to the pool, the next client to use it inherits that exact state. If a previous script set the connection to "read-only" for safety and failed to reset it, any subsequent application attempt to perform an UPDATE or INSERT using that same backend will fail.

Why is the error sporadic?#

The error appears intermittent because it only occurs when your application is randomly assigned a "contaminated" backend connection from the pool. Other connections in the same pool may still be in the default read-write state, leading to a confusing mix of successful and failed requests.

---

Step-by-step resolution#

To resolve this issue, you must identify and remove any commands that modify the session state globally rather than locally.

1. Audit application and scripts#

Search your application code, migration scripts, and maintenance tasks for the following session-level commands:

• SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
• SET default_transaction_read_only = on;

Even if these commands are used in secondary scripts (like data exports or safety-first maintenance tasks) and not the main application, they can still contaminate the pool used by the main application.

2. Implement "safe" settings#

If you need to execute a read-only transaction for safety, use transaction-level commands that only affect the current transaction and do not persist on the backend connection.

• Avoid: SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY; or SET default_transaction_read_only = on; (these contaminate the connection pool)
• Use: BEGIN TRANSACTION READ ONLY; or BEGIN; SET TRANSACTION READ ONLY; (these only affect the current transaction)

If you have existing scripts that use session-level settings and cannot be immediately refactored:

• Temporary workaround: Ensure they explicitly reset the state before closing the connection with SET default_transaction_read_only = off;
• Best approach: Connect directly to port 5432 (bypassing the pooler) for scripts requiring special session states

3. Connection string verification#

Ensure your application is using the intended pooler.

• Shared Transaction Pooler (Supavisor): postgresql://postgres.PROJECT_REF:[YOUR-PASSWORD]@aws-X-REGION.pooler.supabase.com:6543/postgres
• Dedicated Transaction Pooler (PgBouncer): postgresql://postgres:[YOUR-PASSWORD]@db.PROJECT_REF.supabase.co:6543/postgres

---

Best practices for transaction pooling#

• Use Dedicated Connections for Maintenance: If a script requires a specific session state (like a long-running read-only export), connect directly to the database (port 5432) rather than using the transaction pooler (port 6543). This prevents maintenance settings from leaking into the application's connection pool.%