PostgreSQL OperationalError:无法启动事务问题求助（Flask+Docker Compose环境）

Hey there, let's tackle this "Can't start transaction" error you're hitting with your Flask + PostgreSQL Docker setup. This usually pops up when there's an issue with how connections or transactions are being managed between your Flask app and the DB container. Here are the most likely fixes to try out:

A common culprit is mishandling connection creation and cleanup. If you're reusing a global connection object or failing to close connections properly, PostgreSQL might drop idle connections, leading to this error when you try to reuse them later.

Use Flask's request hooks to tie connections to the request lifecycle—this ensures each request gets a fresh (or valid) connection, and it gets closed automatically when the request ends:

from flask import Flask, g
import pgdb

app = Flask(__name__)

def get_db():
    # Store connection in Flask's global request context
    if 'db' not in g:
        g.db = pgdb.connect(
            host='postgres',  # Match your PostgreSQL service name in docker-compose.yml
            database='your_db_name',
            user='your_db_user',
            password='your_db_password'
        )
    return g.db

# Close connection when request finishes
@app.teardown_appcontext
def close_db(error):
    db = g.pop('db', None)
    if db is not None:
        db.close()

# Example route using the connection
@app.route('/run-query')
def run_query():
    cursor = get_db().cursor()
    try:
        cursor.execute("SELECT * FROM your_table")
        results = cursor.fetchall()
        return f"Results: {results}"
    finally:
        cursor.close()

If a previous query threw an exception but you didn't roll back the transaction, the connection can get stuck in a pending state. Subsequent execute calls will fail because PostgreSQL can't start a new transaction until the old one is resolved.

Always commit successful transactions and roll back on errors:

@app.route('/update-data')
def update_data():
    db = get_db()
    cursor = db.cursor()
    try:
        cursor.execute("UPDATE your_table SET value = %s WHERE id = %s", (42, 1))
        db.commit()  # Commit changes if no errors
        return "Update successful!"
    except Exception as e:
        db.rollback()  # Roll back on any exception
        return f"Error: {str(e)}"
    finally:
        cursor.close()

For read-only queries, you can enable autocommit to avoid manual transaction handling:

def get_db():
    if 'db' not in g:
        g.db = pgdb.connect(...)
        g.db.autocommit = True  # Auto-commit read-only queries
    return g.db

PostgreSQL automatically drops idle connections after a period (default idle_in_transaction_session_timeout is 10 minutes). If your Flask app holds connections open for too long without activity, they'll be terminated.

You can tweak these settings in your PostgreSQL container:

1. Create a custom postgres.conf file:

   # Disable idle transaction timeout (or set a larger value like 3600000ms = 1 hour)
   idle_in_transaction_session_timeout = 0
   # Keep TCP connections alive
   tcp_keepalives_idle = 60
   tcp_keepalives_interval = 10
   tcp_keepalives_count = 5
   

2. Mount it in your docker-compose.yml:

   services:
     postgres:
       image: postgres:15
       environment:
         POSTGRES_DB: your_db_name
         POSTGRES_USER: your_db_user
         POSTGRES_PASSWORD: your_db_password
       volumes:
         - ./postgres.conf:/var/lib/postgresql/data/postgresql.conf
         - postgres_data:/var/lib/postgresql/data
       command: postgres -c config_file=/var/lib/postgresql/data/postgresql.conf
   
     flask-app:
       build: ./your-flask-app
       ports:
         - "5000:5000"
       depends_on:
         - postgres
   
   volumes:
     postgres_data:
   

Instead of managing individual connections, use a connection pool to handle reuse, invalidation, and reconnection automatically. Libraries like SQLAlchemy (which works with PostgreSQL) simplify this a lot:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://your_db_user:your_db_password@postgres/your_db_name'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

@app.route('/pooled-query')
def pooled_query():
    results = db.session.execute("SELECT * FROM your_table").fetchall()
    return f"Pooled results: {results}"

SQLAlchemy's built-in connection pool will automatically refresh connections that get dropped by PostgreSQL.

Double-check that your Flask and PostgreSQL containers are on the same Docker network (Docker Compose creates a default network for all services in the file, so this should be okay unless you've customized networks). You can also check PostgreSQL logs for connection termination clues:

docker-compose logs postgres

Look for lines like FATAL: terminating connection due to idle-in-transaction timeout—this confirms idle connections are being dropped.

If none of these fixes work, share your Flask connection code snippet and PostgreSQL container logs, and we can dig deeper!

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