PostgreSQL函数自动Commit/Rollback所带来的问题

一、综述

今天在PostgreSQL遇到一个奇怪的现象，简而言之，是想用函数（存储过程）实现插入记录，整个过程没报错但事后却没找到记录！忙活半天，才发现原因是PostgreSQL函数（存储过程）有自动COMMIT或ROLLBACK的特殊规定。

二、问题重现

以下用示例表和示例代码来重现该问题。

create table t1 
(
  ID int not null primary key,
  name varchar(20)
);

涉及的存储过程是从oracle那边直接拷贝过来后再修改过的，原先是动态SQL，这里简化为静态SQL。注意其中有个commit；根据PostgreSQL的要求，对事务增加begin...exception...end，否则会有错误或警告。示例脚本代码为：

复制代码

create or replace function p1(pid int, pname varchar)
returns void as $$
begin
  begin             --pg对事务的要求
  insert into t1 values(pid, pname);
  commit;
  exception      
  when others then
  end;              --pg对事务的要求
end;
$$ language plpgsql;

依次执行脚本创建存储过程、调用存储过程、查找示例表，结果如下：

postgres=# \i test1.sql
CREATE FUNCTION
postgres=# select p1(1, 'abc');
 p1
----
(1 行记录)

postgres=# select * from t1;

id | name
----+------
(0 行记录)

要插入的记录并不存在！惊喜不惊喜？意外不意外？

三、原因分析及解决

仔细查找有关资料，发现有这么一个解释：

Functions and trigger procedures are always executed within a transaction established by an outer 
query — they cannot start or commit that transaction, since there would be no context for them to 
execute in. However, a block containing an EXCEPTION clause effectively forms a subtransaction that 
can be rolled back without affecting the outer transaction.

其意义是PostgreSQL的函数总是默认为一个事务，总是自动Commit或Rollback。

其实一开始没增加begin...exception...end时，PostgreSQL报错“can't begin/end transaction in pl/pgsql”，已经隐含了这层信息。只是脑子里还是延续Oracle的习惯，而画蛇添足了。

于是，修改存储过程的脚本，按最简单的法子来

create or replace function p2(pid int, pname varchar)
returns void as $$
begin
  insert into t1 values(pid, pname);
end;
$$ language plpgsql;

为验证此说法是否正确，在再次创建函数、调用函数后，增加一个回滚（事先已设置AutoCommit为false）的操作，然后再查询记录：

postgres=# \i test1.sql
CREATE FUNCTION
postgres=# select p2(1, 'abc');
 p2
----
(1 行记录)

postgres=# rollback;
WARNING:  there is no transaction in progress
ROLLBACK
postgres=# select * from t1;
 id | name
----+------
  1 | abc
(1 行记录)

复制代码