如何使用psycopg2执行ALTER SYSTEM这类非事务SQL语句？

你遇到的问题核心是：psycopg2通过with connection上下文管理器操作时，会自动将语句包裹在事务块中，而ALTER SYSTEM属于PostgreSQL的非事务性语句，无法在事务内执行。

解决思路是临时开启连接的自动提交模式，让目标语句直接在事务外执行，执行完成后再恢复原有连接的事务状态，避免影响其他需要事务的操作。

修改现有代码的方案 #

可以给你的DbConnection类新增专门处理非事务性语句的方法，或者扩展现有__execute方法增加事务控制参数：

方案1：新增非事务执行方法

import os
import psycopg2
import psycopg2.extras
from psycopg2._psycopg import connection as pgconnection
from typing import Any, Optional, List, Tuple

class DbConnection:
    con : pgconnection = None

    def connect(self):
        dbname = os.getenv('POSTGRES_DB')
        user = os.getenv('POSTGRES_USER')
        self.con = psycopg2.connect(
            dbname=dbname,
            user=user,
            cursor_factory=psycopg2.extras.RealDictCursor)

    def __execute(self, fetch: bool, query, vars=...) -> Optional[List[Tuple[Any]]]:
        if not self.con or self.con.closed != 0:
            self.connect()
        with self.con as con:
            with con.cursor() as cur:
                cur.execute(query, vars)
                if fetch:
                    return cur.fetchall()
        return None
    
    # 新增非事务性语句执行方法
    def execute_non_transactional(self, query, vars=...) -> None:
        if not self.con or self.con.closed != 0:
            self.connect()
        # 保存原有autocommit状态，执行后恢复
        original_autocommit = self.con.autocommit
        try:
            self.con.autocommit = True
            with self.con.cursor() as cur:
                cur.execute(query, vars)
        finally:
            self.con.autocommit = original_autocommit

执行ALTER SYSTEM时调用这个新增方法：

db = DbConnection()
db.execute_non_transactional("ALTER SYSTEM SET max_connections TO '100';")

方案2：扩展现有方法支持事务控制

修改__execute方法，增加transactional参数来控制是否使用事务：

def __execute(self, fetch: bool, query, vars=..., transactional: bool = True) -> Optional[List[Tuple[Any]]]:
    if not self.con or self.con.closed != 0:
        self.connect()
    
    if transactional:
        # 原有事务内执行逻辑
        with self.con as con:
            with con.cursor() as cur:
                cur.execute(query, vars)
                if fetch:
                    return cur.fetchall()
    else:
        # 非事务模式：临时开启autocommit
        original_autocommit = self.con.autocommit
        try:
            self.con.autocommit = True
            with self.con.cursor() as cur:
                cur.execute(query, vars)
                if fetch:
                    return cur.fetchall()
        finally:
            self.con.autocommit = original_autocommit
    return None

执行ALTER SYSTEM时传入transactional=False：

db = DbConnection()
db._DbConnection__execute(fetch=False, query="ALTER SYSTEM SET max_connections TO '100';", transactional=False)

关键说明 #

• autocommit=True时，psycopg2会将每个SQL语句作为独立的事务自动提交，不会包裹在事务块中，刚好适配ALTER SYSTEM这类非事务性语句。
• 执行ALTER SYSTEM后，需要重启PostgreSQL服务才能让新的配置生效，这是PostgreSQL的特性，和psycopg2无关。

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