PostgreSQL partial/sub commit within function

Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大，国内顶级PostgreSQL数据库专家将悉数到场，并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:

• Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
• Postgres-XL的项目发起人Mason Sharp
• pgpool的作者石井达夫(Tatsuo Ishii)
• PG-Strom的作者海外浩平(Kaigai Kohei)
• Greenplum研发总监姚延栋
• 周正中(德哥), PostgreSQL中国用户会创始人之一
• 汪洋，平安科技数据库技术部经理
• ……

 

2015年度PG大象会报名地址：http://postgres2015.eventdove.com/

PostgreSQL中国社区： http://postgres.cn/

PostgreSQL专业1群： 3336901（已满）

PostgreSQL专业2群： 100910388

PostgreSQL专业3群： 150657323

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)

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