如何在psycopg2中执行非事务块内的SQL语句？

一、解决CREATE INDEX CONCURRENTLY的事务报错 #

你的问题根源是连接创建后才设置autocommit时，默认事务已经自动开启，导致即使开启autocommit，当前仍处于事务块内。正确做法是在连接初始化阶段就确保autocommit生效，有两种实现方式：

方式1：连接后先关闭默认事务再开启autocommit #

#!/usr/bin/env python3.10

import psycopg2 as pg2

connDetails = {'database': None, 'host': None, 'port': None, 'user': None, 'password': None}

with pg2.connect(**connDetails) as conn, conn.cursor() as curs:
    # 先关闭默认开启的事务
    conn.rollback()
    # 开启自动提交模式
    conn.set_session(autocommit=True)
    
    curs.execute("""
create index concurrently if not exists my_new_index on my_table (my_column);
""")

方式2：分开展开with语句，提前设置autocommit #

#!/usr/bin/env python3.10

import psycopg2 as pg2

connDetails = {'database': None, 'host': None, 'port': None, 'user': None, 'password': None}

with pg2.connect(**connDetails) as conn:
    # 连接后立即开启autocommit，避免默认事务启动
    conn.set_session(autocommit=True)
    with conn.cursor() as curs:
        curs.execute("""
create index concurrently if not exists my_new_index on my_table (my_column);
""")

原理：psycopg2默认建立连接后会自动启动一个事务，只有当autocommit=True时，每条SQL语句会独立执行并自动提交，不会被包裹在事务块内。如果先创建连接再设置autocommit，必须先关闭已启动的默认事务，新设置才会生效。

二、解决批量SQL语句的资源锁定问题 #

单次execute()执行多语句时，所有语句处于同一事务，首个语句的锁会持续到整个事务结束。最优解决方案如下：

• 拆分execute()逐个执行：在autocommit模式下，每个execute()执行完成后立即提交并释放锁，这是最直接有效的方式。
• 批量执行示例：

with pg2.connect(**connDetails) as conn:
    conn.set_session(autocommit=True)
    with conn.cursor() as curs:
        statements = [
            "update table1 set col1 = 'val1' where id = 1",
            "update table2 set col2 = 'val2' where id = 2",
            "create index concurrently idx_table3_col3 on table3 (col3)"
        ]
        for stmt in statements:
            curs.execute(stmt)

这种方式每个语句独立运行，不会长时间占用资源锁。

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