PostgreSQL Oracle 兼容性之 - plpgsql, pl/sql 存储过程、函数内事务处理 , block , exception

digoal 2019-05-31

614

TAG 21

作者

digoal

日期

2019-05-31

背景

Oracle用户在函数中或存储过程中会用到自治事务，例如savepoint, rollback to，在处理数据的过程中，根据业务逻辑的需求，选择性的回退变更到设定的savepoint。

PostgreSQL的plpgsql函数语言，与Oracle的使用方法略有不同。

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

首先需要熟悉几个概念

概念

1、一个plpgsql函数是一个独立的大事务。但是内部又可以有子事务(通过block控制)。

2、块(block)

<<block>> declare begin exception end;

一个块被切成了两份，begin和exception，事务状态可能有几种情况：要么begin里面全部提交，要么begin里面全部回滚exception里面全部提交，要么全部回滚

1、当begin里面没有异常时，全部提交

2、当begin里面有异常，exception里面没有异常时，begin里面全部回滚exception里面全部提交

3、begin和exception都有异常时，begin和exception里面全部回滚

了解以上知识点，下面看几个例子

例子

create table t (id int, cnt int); insert into t values (1, 100);

这个表有1条记录，更新CNT，如果更新后的CNT变成负数，就回退这笔更新，并继续。

写一个函数，一共4个BLOCK

block0在最外层，包含了block1,block2,block3

```
create or replace function f_test(int,int,int,int,int) returns void as $$
<>
declare
tmp int;
begin
<>
declare
begin
update t set cnt=cnt-$2 where id=$1 returning cnt into tmp;
if tmp <0 then
raise exception '';
end if;
exception when others then
raise notice 'block1, 已扣减到负数, 回退当前的begin block，并继续后面的BEGIN BLOCK';
end;

<>
declare
begin
update t set cnt=cnt-$3 where id=$1 returning cnt into tmp;
if tmp <0 then
raise exception '';
end if;
exception when others then
raise notice 'block2, 已扣减到负数, 回退当前的begin block';
-- 有没有办法回退到当前BEGIN BLOCK以外的其他位置？并继续后面的BEGIN BLOCK
-- 没有
-- 也就是说如果block1成功了的话，就没法回退block1了，除非在block0的BODY里面回退整个block0
end;

<>
declare
begin
update t set cnt=cnt-$4 where id=$1 returning cnt into tmp;
if tmp <0 then
raise exception '';
end if;
exception when others then
raise notice 'block3, 已扣减到负数, 回退当前的begin block';
end;

update t set cnt=cnt-$5 where id=$1 returning cnt into tmp;
if tmp <0 then
raise exception '';
end if;
exception when others then
raise notice '如果触发当前exception，则回退到顶层begin block之前的状态';
-- 如果到这里，会回退整个block0，包括了block1,block2,block3
end;
$$ language plpgsql strict;
```

例子

1、block0,block1,block2的内容完整执行，block3回退。

```
postgres=# select f_test(1,8,9,200,0);
NOTICE: block3, 已扣减到负数, 回退当前的begin block
f_test

(1 row)

postgres=# select * from t;
id | cnt
----+-----
1 | 83
(1 row)
```

2、block0,block1,block2的内容完整执行，block3回退。

```
postgres=# select f_test(1,8,9,200,0);
NOTICE: block3, 已扣减到负数, 回退当前的begin block
f_test

(1 row)

postgres=# select * from t;
id | cnt
----+-----
1 | 66
(1 row)
```

3、block0,block1的内容完整执行，block2, block3回退。

```
postgres=# select f_test(1,8,900,200,0);
NOTICE: block2, 已扣减到负数, 回退当前的begin block
NOTICE: block3, 已扣减到负数, 回退当前的begin block
f_test

(1 row)

postgres=# select * from t;
id | cnt
----+-----
1 | 58
(1 row)
```

4、block0,block1，block2, block3的内容完整执行。

```
postgres=# select f_test(1,8,9,2,0);
f_test

(1 row)

postgres=# select * from t;
id | cnt
----+-----
1 | 39
(1 row)
```

5、block0全部回退。

```
postgres=# select f_test(1,8,9,2,100);
NOTICE: 如果触发当前exception，则回退到顶层begin block之前的状态
f_test

(1 row)

postgres=# select * from t;
id | cnt
----+-----
1 | 39
(1 row)
```

问题

有没有办法在一个block中回退到当前BEGIN BLOCK以外的其他位置的状态？并继续执行后面的BEGIN BLOCK

没有

所以，下面这张方式，目前没有办法实现。

```
create or replace function f_test(int,int,int,int) returns void as $$
<>
declare
tmp int;
savepoint a; -- 目前没法实现
begin
<>
declare
begin
update t set cnt=cnt-$2 where id=$1 returning cnt into tmp;
if tmp <0 then
raise exception '';
end if;
exception when others then
raise notice 'block1, 已扣减到负数, 回退当前的begin block，并继续后面的BEGIN BLOCK';
rollback to a; -- 目前没法实现,这里只能回退block1的body
end;

<>
declare
begin
update t set cnt=cnt-$3 where id=$1 returning cnt into tmp;
if tmp <0 then
raise exception '';
end if;
exception when others then
raise notice 'block2, 已扣减到负数, 回退当前的begin block';
-- 有没有办法回退到当前BEGIN BLOCK以外的其他位置？并继续后面的BEGIN BLOCK
-- 没有
rollback to a; -- 目前没法实现,这里只能回退block2的body
end;

exception when others then
raise notice '如果触发当前exception，则回退到顶层begin block之前的状态';
end;
$$ language plpgsql strict;
```

也许将来的版本会支持以上用法。

其他，子事务拆分成多个函数，通过DBLINK调用，通过dblink或notify,listen同步不同子事务的完成情况。

拆分子事务存储过程

savepoint

rollback

监测子事务状态(使用notify或dblink，同步状态)，选择回退或继续

需要注意notify lock问题

https://www.postgresql-archive.org/PATCH-Improve-performance-of-NOTIFY-over-many-databases-issue-blocking-on-AccessExclusiveLock-on-obj-td6088908.html