PostgreSQL Oracle兼容性之 - 函数 自治事务 的实现-阿里云开发者社区

开发者社区> 德哥> 正文

PostgreSQL Oracle兼容性之 - 函数 自治事务 的实现

简介: 使用Oracle的用户,在函数中如果使用了自治事务的话,如果要转到PostgreSQL会遇到很棘手的问题。 因为PostgreSQL的函数是作为一个事务来处理的,要么全部提交,要么全部回滚,除了exception,每个exception是一个子事务。 使用exception确实可以达到类似自治事
+关注继续查看

使用Oracle的用户,在函数中如果使用了自治事务的话,如果要转到PostgreSQL会遇到很棘手的问题。
因为PostgreSQL的函数是作为一个事务来处理的,要么全部提交,要么全部回滚,
除了exception,每个exception是一个子事务。
因此使用exception可以达到自治事务的目的。
例子1,
使用并行block和嵌套block,来控制子事务层级。
输入参数为block1, block2.1, block2.2, block3.1 。
这些参数代表执行在哪个block出错,出错时对应层级的block的exception会捕获错误,同时处理,然后跳到下一个block继续执行。
如果是外层的block出错,内层还没有被执行的block就没机会执行了。
根据业务需求,调整block层级或嵌套层级,达到目的。
这种用法可以完美的支撑业务的需求。
(除了一种情况不能满足,就是被提交的子事务立刻可以被其他事务可见。这种需求建本文下面的方法,用dblink来满足这种需求即可。)

create or replace function ft(err_level text) returns void as 
$$

declare
begin -- block level 1
  raise notice 'block level 1';
  if (err_level='block1') then
    raise exception '%', err_level;
  end if;

  begin -- block level 2.1
    raise notice 'block level 2.1';  -- 请用业务处理SQL代替
    if (err_level='block2.1') then
      raise exception '%', err_level;
    end if;

    begin -- block level 3.1
      raise notice 'block level 3.1';
      if (err_level='block3.1') then
        raise exception '%', err_level;
      end if;
      exception when others then  -- you can write catchup any ERROR CODE or ERROR STATE.
        raise notice 'end block level 3.1';
    end; -- end block level 3.1

    exception when others then  -- you can write catchup any ERROR CODE or ERROR STATE. 回滚block 2.1的业务处理SQL
      raise notice 'end block level 2.1';
  end; -- end block level 2.1

  begin -- block level 2.2
    raise notice 'block level 2.2';
    if (err_level='block2.2') then
      raise exception '%', err_level;
    end if;
    exception when others then  -- you can write catchup any ERROR CODE or ERROR STATE.
      raise notice 'end block level 2.2';
  end; -- end block level 2.2

  exception when others then  -- you can write catchup any ERROR CODE or ERROR STATE.
    raise notice 'end block level 1';
end; -- end block level 1

$$
 language plpgsql;

测试:
在block 1出错,出错代码后面的代码都不会被执行。

postgres=# select ft('block1');
NOTICE:  block level 1
NOTICE:  end block level 1
 ft 
----
 
(1 row)

在block2.1出错,block 2.1内部出错代码后面的代码都不会被执行。但是同级代码如2.2会被执行。

postgres=# select ft('block2.1');
NOTICE:  block level 1
NOTICE:  block level 2.1
NOTICE:  end block level 2.1
NOTICE:  block level 2.2
 ft 
----
 
(1 row)

在block2.2出错。

postgres=# select ft('block2.2');
NOTICE:  block level 1
NOTICE:  block level 2.1
NOTICE:  block level 3.1
NOTICE:  block level 2.2
NOTICE:  end block level 2.2
 ft 
----
 
(1 row)

在block3.1出错。

postgres=# select ft('block3.1');
NOTICE:  block level 1
NOTICE:  block level 2.1
NOTICE:  block level 3.1
NOTICE:  end block level 3.1
NOTICE:  block level 2.2
 ft 
----
 
(1 row)

更直观的例子:

drop table tt;
create table tt(id int primary key, info text);
insert into tt values(5,'test');

create or replace function ft() returns void as 
$$

declare
begin -- block level 1

  begin -- block level 2.1
    insert into tt values (1,'test'),(2,'test'),(3,'test');
    exception when others then 
      raise notice 'rollback block level 2.1';
  end; -- end block level 2.1

  begin -- block level 2.2
    insert into tt values (4,'test'),(5,'test'),(6,'test'); -- 主键冲突, 插入失败, 但是不影响后面的block继续执行.
    exception when others then 
      raise notice 'rollback block level 2.2';
  end; -- end block level 2.2

  begin -- block level 2.3
    insert into tt values (7,'test'),(8,'test'),(9,'test');
    exception when others then 
      raise notice 'rollback block level 2.3';
  end; -- end block level 2.3

  exception when others then  -- you can write catchup any ERROR CODE or ERROR STATE.
    raise notice 'rollback block level 1';
end; -- end block level 1

$$
 language plpgsql;

postgres=# select ft();
NOTICE:  rollback block level 2.2
 ft 
----
 
(1 row)

postgres=# select * from tt;
 id | info 
----+------
  5 | test
  1 | test
  2 | test
  3 | test
  7 | test
  8 | test
  9 | test
(7 rows)

例子2:
使用dblink,同样需要将需要批量提交的部分写成子函数先。
例如 :

create extension dblink;
CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '127.0.0.1', dbname '函数所在的库名');
CREATE USER MAPPING FOR 需要调用函数的用户名 SERVER fdtest OPTIONS (user '需要调用函数的用户名', password '用户密码');
GRANT USAGE ON FOREIGN SERVER fdtest TO 需要调用函数的用户名;

函数体

declare
  dblink_block_res1 record;
  dblink_block_res2 record;
...
  dblink_block_resn record;
...
  其他变量定义;
begin
-- 建立连接
if ( dblink_connect('myconn', 'fdtest') <> 'OK' ) then
  raise notice '连接失败';
  return;
end if;
-- block 1,需要流转的变量通过参数传入下面的函数
  select dblink('myconn', 'select func1($1,$2,...)') into dblink_block_res1;  -- $1,$2,...使用常数替代, 或动态SQL
-- 中间结果判断
  if not found then  -- dblink调用异常
    -- 异常处理
  else
    -- dblink对应子事务已提交
  end if;
-- block 2,需要流转的变量通过参数传入下面的函数
  select dblink('myconn', 'select func2($1,$2,...)') into dblink_block_res2;  -- $1,$2,...使用常数替代, 或动态SQL
-- 中间结果判断
  if not found then  -- 远程调用异常
    -- 异常处理
  else
    -- dblink对应子事务已提交
  end if;
......
-- block n,需要流转的变量通过参数传入下面的函数
  select dblink('myconn', 'select funcn($1,$2,...)') into dblink_block_resn;  -- $1,$2,...使用常数替代, 或动态SQL
-- 中间结果判断
  if not found then  -- 远程调用异常
    -- 异常处理
  else
    -- dblink对应子事务已提交
  end if;
......
exception when others then
...
end;

其他参考地址:
http://www.postgresql.org/docs/9.5/static/contrib-dblink-function.html
http://postgresql.nabble.com/Autonomous-Transaction-WIP-td5798928.html
https://lwn.net/Articles/648973/

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
PostgreSQL Oracle 兼容性之 - PL/SQL FORALL, BULK COLLECT
Oracle PL/SQL 开发的童鞋,一定对O家的bulk批量处理的性能很是赞赏吧。但是PostgreSQL用户请不要垂涎,作为学院派和工业界的一颗璀璨明珠。开源数据库PostgreSQL,也有对应的批量处理策略哦。下面是一组LOOP和BULK的性能测试数据 一起来耍耍吧,先看看Oracle怎么
3763 0
如果分配给命令的连接位于本地挂起事务中,ExecuteReader 要求命令拥有事务。命令的 Transaction 属性尚未初始化
{System.InvalidOperationException: 如果分配给命令的连接位于本地挂起事务中,ExecuteReader 要求命令拥有事务。命令的 Transaction 属性尚未初始化。
1945 0
Redis事务:用法,常见错误和API
Redis事务:用法,常见错误和API
2195 0
oracle、sqlserver、mysql常用函数对比[to_char、to_number、to_date]
oracle                                       -->                             mysql to_char(sysdate,'yyyy-mm-dd')-->date_format(sysdate(),'%Y-%m-%d');t...
1616 0
ArcGIS 客户端跨版本连接Oracle 地理数据库时的兼容性说明
很多用户都会有这样的疑问: 1:我是否可以使用ArcGIS Desktop9.3连接ArcSDE10 2:我是否可以使用ArcGIS 10.
947 0
PostgreSQL的事务隔离分析
隔离级别(Isolation levels) 有四种隔离级别: 可序列化(Serializable) 可重复读(Repeatable reads) 提交读(Read committed) 未提交读(Read uncommitted) ...
2346 0
事务实现,redo,undo,锁
事务(Transaction)是数据库区别于文件系统的重要特性之一。在文件系统中,如果你正在写文件,但是操作系统突然崩溃了,这个文件就很有可能被破坏。当然,有一些机制可以把文件恢复到某个时间点。不过,如果需要保证两个文件同步,这些文件系统可能就显得无能为力了。
698 0
+关注
德哥
公益是一辈子的事, I&#39;m digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载