PostgreSQL函数自动Commit/Rollback；Oracle与postgreSQL函数commit和rollback的不同；oralce和pg函数事物区别

曾经终败给现在 2023-02-25 02:21 29阅读 0赞

## 一、综述 ##

今天在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 行记录)

可见，这次记录已成功插入，且外部的回滚操作对其无影响。

## 四、总结 ##

Oracle是可以在存储过程或函数里指定Commit/Rollback的，如果没有，则外部调用者可以回滚存储过程内部的操作。

但在PostgreSQL，函数（存储过程）总是自动将其所有操作当作一个事务，外部无法对内部操作提交或回滚。

问题好像已经解决，但留有一个疑问没弄明白，为什么PostgreSQL允许在函数体中加关于事务的begin...exception...end，但结果却好像是没提交？