PostgreSQL Oracle 兼容性 自治事务 - partial/sub commit within function

背景

PostgreSQL的函数是原子操作，所以不能像Oracle那样在函数中实现分段提交。

但是如果你要从Oracle迁移到PostgreSQL的话，必然会面临这样的问题，那么怎么办呢？

有几种方法可以来实现，下面是例子：

1. 通过exception来实现分段提交。

create table tbl (id int primary key, info text);  
  
create or replace function func1() returns void as $$  
declare  
  v_stat int := 0;  
begin  
  insert into tbl values(1);  
  v_stat := 1;  
  insert into tbl values(2);  
  v_stat := 2;  
  insert into tbl values(3);  
  v_stat := 3;  
  insert into tbl values(3);  
  v_stat := 4;  
  return;  
  exception when others then  
    case   
      when v_stat = 1 then  
        insert into tbl values(1);  
      when v_stat = 2 then  
        insert into tbl values(1);  
        insert into tbl values(2);  
      when v_stat = 3 then  
        insert into tbl values(1);  
        insert into tbl values(2);  
        insert into tbl values(3);  
      when v_stat = 4 then  
        insert into tbl values(1);  
        insert into tbl values(2);  
	insert into tbl values(3);  
	insert into tbl values(3);  
      else  
        return;  
      end case;  
end;  
$$ language plpgsql;  
  
postgres=# select func1();  
 func1   
-------  
   
(1 row)  
  
postgres=# select ctid,* from tbl;  
 ctid  | id | info   
-------+----+------  
 (0,5) |  1 |   
 (0,6) |  2 |   
 (0,7) |  3 |   
(3 rows)  

这样做的弊端很明显，需要重新插入，相当于前面的操作全部回滚，产生了双份数据，其中前面插入的一份是垃圾。

另外，代码会变的很繁琐。

所以我们可以略微改进一下。

2. 把每个分段作为一个子函数，正常返回true，异常返回false，在函数中调用这些子函数来实现分段来规避上面的问题。

create or replace function subf1() returns boolean as $$  
declare  
begin  
  insert into tbl values(1);  
  -- 以及其他需要放一起提交的SQL和逻辑  
  return true;  
exception when others then  
  return false;  
end;   
$$ language plpgsql strict;  
  
create or replace function subf2() returns boolean as $$  
declare  
begin  
  insert into tbl values(2);  
  -- 以及其他需要放一起提交的SQL和逻辑  
  return true;  
exception when others then  
  return false;  
end;   
$$ language plpgsql strict;  
  
create or replace function subf3() returns boolean as $$  
declare  
begin  
  insert into tbl values(3);  
  -- 以及其他需要放一起提交的SQL和逻辑  
  return true;  
exception when others then  
  return false;  
end;   
$$ language plpgsql strict;  
  
create or replace function subf4() returns boolean as $$  
declare  
begin  
  insert into tbl values(3);  
  -- 以及其他需要放一起提交的SQL和逻辑  
  return true;  
exception when others then  
  return false;  
end;   
$$ language plpgsql strict;  
  
  
  
create or replace function func1() returns void as $$  
declare  
  v_stat boolean;  
begin  
--  模拟分段1  
  select subf1() into v_stat;  
  if not v_stat then   
    return;  
  end if;  
--  模拟分段2  
  select subf2() into v_stat;  
  if not v_stat then   
    return;  
  end if;  
--  模拟分段3  
  select subf3() into v_stat;  
  if not v_stat then   
    return;  
  end if;  
--  模拟分段4  
  select subf4() into v_stat;  
  if not v_stat then   
    return;  
  end if;  
return;  
end;  
$$ language plpgsql;  
  
  
postgres=# truncate tbl;  
TRUNCATE TABLE  
postgres=# select func1();  
 func1   
-------  
   
(1 row)  
  
postgres=# select ctid,* from tbl;  
 ctid  | id | info   
-------+----+------  
 (0,1) |  1 |   
 (0,2) |  2 |   
 (0,3) |  3 |   
(3 rows)  

现在正常了，不会产生双份垃圾。

PostgreSQL 11已支持函数内COMMIT，支持自治事务。

https://www.postgresql.org/docs/devel/static/plpgsql-porting.html

digoal’s 大量PostgreSQL文章入口