Compare dblink module Within One Transaction in PostgreSQL - dblink, 自治事务(Oracle兼容性)

背景

PostgreSQL plpgsql函数为原子调用，如果需要实现阶段性提交，可以通过dblink调用，实现自治事务。

dblink实现自治事务

PostgreSQL 9.0.3  
RHEL 5 x86_64  

在PostgreSQL的存储函数中不支持自治事务，啥意思呢？也就是说一个存储过程里的SQL被认为是一个事务单元来处理。要么全部成功要么全部失败。

但是通过这2种方法可以开启独立的事务：

dblink和exception 。

each BEGIN/EXCEPTION/END block creates a subtransaction.   

下面看看PostgreSQL中dblink在事务中的使用.(实际上PostgreSQL中使用dblink和本地事务是隔离的，算是另外开启了一个会话，另起事务).

例一（使用stable函数验证，实际上使用当前事务号验证也可以，即txid_current()）:

digoal=> select now();begin;select pg_sleep(3);select now();select pg_sleep(3);select now();end;  
              now                
-------------------------------  
 2011-02-14 10:29:34.924432+08  
(1 row)  
  
BEGIN  
 pg_sleep   
----------  
   
(1 row)  
  
              now                
-------------------------------  
 2011-02-14 10:29:34.924645+08  
(1 row)  
  
 pg_sleep   
----------  
   
(1 row)  
  
              now                
-------------------------------  
 2011-02-14 10:29:34.924645+08  
(1 row)  
  
COMMIT  

now()取到的是事务开始的系统时间(这和now()函数的稳定性有关, now的稳定性是stable, 在事务中取值一致不会变化).

换到dblink下取这个时间看看是什么情况,

select * from dblink_connect('lk_test','hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal');  
digoal=> begin;  
BEGIN  
digoal=> select * from dblink('lk_test','select now()') as t(v_time timestamp with time zone);  
            v_time               
-------------------------------  
 2011-02-14 10:36:17.582965+08  
(1 row)  
  
digoal=> select * from dblink('lk_test','select now()') as t(v_time timestamp with time zone);  
            v_time               
-------------------------------  
 2011-02-14 10:36:23.939499+08  
(1 row)  
  
digoal=> end;  

从 now() 函数的返回, 可以看出dblink执行时, 远端使用了autocommit, 所以每次调用dblink在远端都是一个新的事务, 因此now()返回时间是变的.

你可以在远端的pg_stat_activity验证这一点.

换个例子更能说明情况.

digoal=> begin;  
BEGIN  
digoal=> select * from dblink_exec('lk_test','begin;insert into tbl_user (id) values(1),(2);commit;');  
 dblink_exec   
-------------  
 COMMIT  
(1 row)  
  
digoal=> insert into tbl_user (id) values(3);  
ERROR:  duplicate key value violates unique constraint "tbl_user_pkey"  
DETAIL:  Key (id)=(3) already exists.  
digoal=> end;  
ROLLBACK  
digoal=> select * from dblink('lk_test','select id from tbl_user where id in (1,2)') as t (id int);  
 id   
----  
  1  
  2  
(2 rows)  

因为远端是autocommit的, 所以远程执行成功，本地执行失败，事务回滚，但是远程已经提交.

如有要让远端在一个事务中执行, 我们可以使用begin;

postgres=# select * from dblink_exec('lk_test','begin;');  
 dblink_exec   
-------------  
 BEGIN  
(1 row)  

远端提交和回滚

select * from dblink_exec('lk_test','commit;');  
select * from dblink_exec('lk_test','rollback;');  

在plpgsql函数中，对需要阶段性提交的事务，封装在另一个函数中，然后使用dblink调用那个函数，可以实现自治事务的效果。

subfunc1(参数) 返回值.  
subfunc2(参数) 返回值.  

如下方法实现自治事务

func  
declare  
begin  
  select dblink(调用subfunc1(参数)) into 返回值;  
  ....  
end;  

exception实现自治事务

严格来说exception并不是自治事务，因为如果整个函数在调用过程有失败，所有的操作都会回滚，只留下当前exception中正常处理的逻辑。

func  
declare  
begin  
  -- do something  
  -- 一些变量标记  
exception when xxx then  
  -- do something , 如果前面失败，这里可以做一些什么，让整个过程继续  
  -- 一些变量标记  
end;  
  
-- 变量标记判断  
-- 实现一些自治逻辑  
  
begin  
  -- do something  
  -- 一些变量标记  
exception when xxx then  
  -- do something , 如果前面失败  
  -- 一些变量标记  
end;  
  
....  
  

推荐还是使用dblink的模式来实现自治事务。

PostgreSQL 11开始，会支持真正意义的语法层面自治事务。通过开启work process，开启新的子事务来实现。

《PostgreSQL 10.0 preview 功能增强 - 匿名、自治事务(Oracle 兼容性)(background session)》

《PostgreSQL Oracle 兼容性之 - plpgsql 自治事务(autonomous_transaction)补丁》

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

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

digoal’s 大量PostgreSQL文章入口