Absolutely, this is absolutely solvable — let’s break down the best approaches to detect and discard connections to read-only nodes after a failover, so your Flask app’s write operations stay reliable.

Approach 1: Catch Read-Only Errors and Invalidate Connections via SQLAlchemy Events #

SQLAlchemy’s connection pool reuses existing connections by default, which means a connection pointing to a now-read-only node will stick around until it’s explicitly marked invalid. You can use SQLAlchemy’s event system to trap the ReadOnlySqlTransaction exception and tell the pool to discard that faulty connection immediately.

Here’s how to implement it:

from sqlalchemy import event
from sqlalchemy.engine import Engine
import psycopg2

@event.listens_for(Engine, "handle_error")
def handle_read_only_transaction_error(exception_context):
    # Check if the root error is a read-only transaction issue
    if isinstance(exception_context.original_exception, psycopg2.errors.ReadOnlySqlTransaction):
        # Mark the connection as invalid so the pool won't reuse it
        exception_context.connection.invalidate()

This event listener triggers whenever an error occurs in the engine. When it detects the read-only error, it tells the connection pool to discard the problematic connection — the next time your app needs a connection, it’ll grab a fresh one (hopefully from the writable primary node).

Approach 2: Pre-Validate Connections with pool_pre_ping (Preventive Measure) #

To catch stale/read-only connections before they’re used for a write operation, enable SQLAlchemy’s pool_pre_ping feature. This tells the pool to run a quick check on a connection before handing it to your app. By default, it just checks if the connection is alive, but you can customize the check to verify write access too.

Option A: Use a Connection Option to Enforce Read-Write Mode

You can pass a PostgreSQL connection option to force the transaction out of read-only mode — if the node is read-only, this will fail immediately, and the pool will discard the connection:

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://your_user:your_pass@your_cluster_host/your_db",
    pool_pre_ping=True,
    pool_recycle=300,  # Recycle connections every 5 minutes as a safeguard
    connect_args={
        "options": "-c default_transaction_read_only=off"
    }
)

Option B: Custom Ping Check for Write Access

For more control, define a custom function to test if the connection can perform write operations:

def validate_writable_connection(conn):
    try:
        # Execute a trivial, non-destructive write operation
        cursor = conn.cursor()
        cursor.execute("CREATE TEMP TABLE IF NOT EXISTS temp_write_check (id INT);")
        conn.commit()
        return True
    except psycopg2.errors.ReadOnlySqlTransaction:
        conn.rollback()
        return False

engine = create_engine(
    "postgresql://your_user:your_pass@your_cluster_host/your_db",
    pool_pre_ping=True,
    pool_pre_ping_check=validate_writable_connection,
    pool_recycle=300
)

This ensures that only connections capable of write operations are handed to your app.

Approach 3: Flask Request-Level Error Handling #

For a more user-friendly experience, add a global error handler in Flask to catch the read-only error, invalidate the bad connection, and optionally retry idempotent requests:

from flask import Flask, request
import psycopg2
from your_db_setup import db  # Assuming you're using Flask-SQLAlchemy

app = Flask(__name__)

@app.errorhandler(psycopg2.errors.ReadOnlySqlTransaction)
def handle_read_only_request_error(e):
    # Invalidate the current connection to remove it from the pool
    db.session.connection().invalidate()
    db.session.rollback()

    # Retry idempotent requests (GET/HEAD) automatically
    if request.method in ["GET", "HEAD"]:
        return app.full_dispatch_request()
    
    # For non-idempotent requests (POST/PUT/DELETE), return a 503 and let the user retry
    return "Service temporarily unavailable. Please retry your request shortly.", 503

This handles the error gracefully for end users while cleaning up the faulty connection.

Best Practices #

• Combine Approach 1 and Approach 2: Use pool_pre_ping to prevent bad connections from being used, and the error event listener to catch any slips through the cracks.
• Adjust pool_recycle to match your database’s connection timeout settings — this prevents stale connections from hanging around.
• If you’re using a database cluster with a load balancer, ensure the load balancer is correctly routing write traffic to the primary node (this reduces the chance of hitting a read-only node in the first place).

内容的提问来源于stack exchange，提问作者kenshin23