为何PL/Python函数对PostgreSQL事务计数器的影响远大于PL/pgSQL？

现象描述 #

对比测试显示，相同业务逻辑下，PL/Python函数对事务计数器（txid_current()）的消耗远高于PL/pgSQL函数：

• 调用PL/pgSQL函数100次执行插入操作，事务计数器仅增加1（从500到502，差值为2，其中1次为当前事务的正常消耗）
• 替换为PL/Python实现的同逻辑函数后，事务计数器直接增加100（从500到602，差值为102）

PL/pgSQL测试代码及结果 #

CREATE TABLE insert_rows_table(
    i BIGINT
);

CREATE OR REPLACE FUNCTION insert_row_to_db(i BIGINT)
RETURNS VOID
AS $$
BEGIN
    INSERT INTO insert_rows_table SELECT i;
END
$$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE PARALLEL UNSAFE;

CREATE OR REPLACE FUNCTION f1_plpgsql(i BIGINT)
  RETURNS bigint
AS $$
BEGIN
    PERFORM insert_row_to_db(i);
    RETURN i;
END
$$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE PARALLEL UNSAFE;

-- 分独立事务执行以下语句
SELECT txid_current();
SELECT f1_plpgsql(i::BIGINT) FROM generate_series(1,100) as i;
SELECT txid_current();

输出结果：

txid_current
500

f1_plpgsql
1
2
...
99
100

txid_current
502

PL/Python测试代码及结果 #

替换上述f1_plpgsql为PL/Python版本：

CREATE OR REPLACE FUNCTION f1_plpython(i BIGINT)
  RETURNS bigint
AS $$
    rows = plpy.execute("SELECT insert_row_to_db(" + str(i) + ")")
    return i
$$ LANGUAGE plpython3u SECURITY DEFINER VOLATILE PARALLEL UNSAFE;

输出结果：

txid_current
500

f1_plpython
1
2
...
99
100

txid_current
602

注：仅当PL/Python调用的函数涉及数据库修改时才会出现此差异；若将INSERT替换为只读的SELECT，两者事务计数器表现完全一致。

原因分析 #

1. PL/pgSQL的原生事务整合特性
   PL/pgSQL是PostgreSQL原生过程语言，完全运行在数据库主事务上下文内。当在单个SQL查询中多次调用PL/pgSQL函数时，所有DML操作都隶属于同一个顶层事务，仅会生成一个事务ID，因此事务计数器仅增长1。

2. PL/Python的子事务触发机制
   PL/Python属于外部扩展语言，每次通过plpy.execute()执行修改类SQL语句时，PostgreSQL会为每一次执行创建并提交一个独立的子事务。每个子事务都会消耗一个新的事务ID，100次调用就会消耗100个事务ID，最终导致计数器大幅增长。

优化方法 #

1. 批量处理DML操作
   避免在PL/Python中循环执行单个插入/更新，改为一次性生成批量SQL语句，通过单次plpy.execute()完成所有修改。示例：

CREATE OR REPLACE FUNCTION f1_plpython_batch()
  RETURNS void
AS $$
    # 生成批量插入语句
    insert_sql = "INSERT INTO insert_rows_table SELECT * FROM generate_series(1,100) as i;"
    plpy.execute(insert_sql)
$$ LANGUAGE plpython3u SECURITY DEFINER VOLATILE PARALLEL UNSAFE;

2. 将修改逻辑委托给PL/pgSQL
   让PL/Python仅处理业务逻辑，调用PL/pgSQL编写的批量处理函数，利用PL/pgSQL的事务整合特性减少事务ID消耗。

3. 合并SQL调用
   尽量将多次plpy.execute()调用合并为一次，减少子事务的创建次数，从而降低事务计数器的消耗。

内容的提问来源于stack exchange，提问作者Michał Albrycht