关于数据库:MogDB存储过程事务控制与异常块

59次阅读

共计 6042 个字符，预计需要花费 16 分钟才能阅读完成。

本文将别离在 openGauss/MogDB 和 PostgreSQL 数据库中测试存储过程 commit 与 exception 的应用。

试验一
1.PostgreSQL
先创立测试表：

create table t1(id int);

上面创立存储过程 proc1：

create or replace procedure proc1() as
$$
declare
begin
    raise notice '--begin to drop table t2,time=%',clock_timestamp();
    drop table if exists t2;
    raise notice '--drop table t2,time=%',clock_timestamp();
    
    raise notice '--begin to alter table t1 rename to t2,time=%',clock_timestamp();    
    alter table t1 rename to t2;
    raise notice '--alter table t1 rename to t2,time=%',clock_timestamp();    
    
    raise notice '--do something...,time=%',clock_timestamp();    
    perform pg_sleep(30);
    
    raise notice '--begin to commit,time=%',clock_timestamp();
    commit;
    raise notice '-- commit over,time=%',clock_timestamp();
    
    raise notice 'job is over,time=%',clock_timestamp();
end;
$$ language plpgsql;

残缺的执行后果如下：

postgres=# call proc1();
NOTICE:  --begin to drop table t2,time=2021-12-22 17:11:52.746994+08
NOTICE:  table "t2" does not exist, skipping
NOTICE:  --drop table t2,time=2021-12-22 17:11:52.747054+08
NOTICE:  --begin to alter table t1 rename to t2,time=2021-12-22 17:11:52.747057+08
NOTICE:  --alter table t1 rename to t2,time=2021-12-22 17:11:52.74728+08
NOTICE:  --do something...,time=2021-12-22 17:11:52.74729+08
NOTICE:  --begin to commit,time=2021-12-22 17:12:22.778001+08
NOTICE:  -- commit over,time=2021-12-22 17:12:22.778074+08
NOTICE:  job is over,time=2021-12-22 17:12:22.778081+08
CALL
Time: 30031.268 ms (00:30.031)

下面的程序代码块里，咱们应用 commit 语句是为了确保 t1 表的改名操作能够立刻对其它客户端可见，同时为了便于测试察看，咱们在 commit 语句之前加了一个 30 秒的延时。

上面察看如果在这个提早的工夫内，如果有新的客户端拜访 t1 表，是什么景象，测试后果如下图：


能够看到新的客户端拜访 t1 表会产生锁期待(截图中的左下和右下局部)。

2.openGauss/MogDB
先创立测试表：

create table t1(id int);

上面创立存储过程 proc1：

create or replace procedure proc1() as
begin
    raise notice '--begin to drop table t2,time=%',clock_timestamp();
    drop table if exists t2;
    raise notice '--drop table t2,time=%',clock_timestamp();
    
    raise notice '--begin to alter table t1 rename to t2,time=%',clock_timestamp();    
    alter table t1 rename to t2;
    raise notice '--alter table t1 rename to t2,time=%',clock_timestamp();    
    
    raise notice '--do something...,time=%',clock_timestamp();    
    perform pg_sleep(30);
    
    raise notice '--begin to commit,time=%',clock_timestamp();
    commit;
    raise notice '-- commit over,time=%',clock_timestamp();
    
    raise notice 'job is over,time=%',clock_timestamp();
end;
/

残缺的执行后果如下：

postgres=# call proc1();
NOTICE:  --begin to drop table t2,time=2021-12-22 17:37:40.72122+08
NOTICE:  table "t2" does not exist, skipping
CONTEXT:  SQL statement "drop table if exists t2"
PL/pgSQL function proc1() line 4 at SQL statement
NOTICE:  --drop table t2,time=2021-12-22 17:37:40.721364+08
NOTICE:  --begin to alter table t1 rename to t2,time=2021-12-22 17:37:40.721404+08
NOTICE:  --alter table t1 rename to t2,time=2021-12-22 17:37:40.721835+08
NOTICE:  --do something...,time=2021-12-22 17:37:40.721917+08
NOTICE:  --begin to commit,time=2021-12-22 17:38:10.723386+08
NOTICE:  -- commit over,time=2021-12-22 17:38:11.072483+08
NOTICE:  job is over,time=2021-12-22 17:38:11.072545+08

程序代码块里应用 commit 语句是为了确保 t1 表的改名操作能够立刻对其它客户端可见，为了便于测试察看，咱们在 commit 语句之前加了一个 30 秒的延时。

上面察看如果在这个提早的工夫内，如果有新的客户端拜访 t1 表，是什么景象，测试后果如下图：

能够看到新的客户端拜访 t1 表会产生锁期待(截图中的左下和右下局部)。

试验二
对下面的 proc1 减少 exception 解决，批改后的代码如下：

1.PostgreSQL

先创立测试表：

create table t1(id int);

上面创立存储过程 proc2：

create or replace procedure proc2() as
$$
declare
begin
    raise notice '--begin to drop table t2,time=%',clock_timestamp();
    drop table if exists t2;
    raise notice '--drop table t2,time=%',clock_timestamp();
    
    raise notice '--begin to alter table t1 rename to t2,time=%',clock_timestamp();    
    alter table t1 rename to t2;
    raise notice '--alter table t1 rename to t2,time=%',clock_timestamp();    
    
    raise notice '--do something...,time=%',clock_timestamp();    
    perform pg_sleep(30);
    
    raise notice '--begin to commit,time=%',clock_timestamp();
    commit;
    raise notice '-- commit over,time=%',clock_timestamp();
    
    raise notice 'job is over,time=%',clock_timestamp();
    
    exception when others then
        raise notice 'sqlstate=%,sqlerrm=%', sqlstate,sqlerrm;        
end;
$$ language plpgsql;

残缺的执行后果如下：

postgres=# call proc2();
NOTICE:  --begin to drop table t2,time=2021-12-22 17:48:56.030816+08
NOTICE:  --drop table t2,time=2021-12-22 17:48:56.031055+08
NOTICE:  --begin to alter table t1 rename to t2,time=2021-12-22 17:48:56.031082+08
NOTICE:  --alter table t1 rename to t2,time=2021-12-22 17:48:56.031242+08
NOTICE:  --do something...,time=2021-12-22 17:48:56.031269+08
NOTICE:  --begin to commit,time=2021-12-22 17:49:26.09492+08
NOTICE:  sqlstate=2D000,sqlerrm=cannot commit while a subtransaction is active
CALL
Time: 30064.663 ms (00:30.065)

能够看出，如果咱们的语句块里有 exception 子句，那当咱们调用 commit 语句则会提醒谬误：

cannot commit while a subtransaction is active

其实在官网文档有如下相干的形容：

A transaction cannot be ended inside a block with exception handlers.

参考链接如下：https://www.postgresql.org/do…

因而在 PG 外面，咱们不能再有 exception 子句的存储过程应用 commit 或者 rollback 语句。

2.openGauss/MogDB

先创立测试表：

create table t1(id int);

再创立存储过程 proc2：

create or replace procedure proc2() as
begin
    raise notice '--begin to drop table t2,time=%',clock_timestamp();
    drop table if exists t2;
    raise notice '--drop table t2,time=%',clock_timestamp();
    
    raise notice '--begin to alter table t1 rename to t2,time=%',clock_timestamp();    
    alter table t1 rename to t2;
    raise notice '--alter table t1 rename to t2,time=%',clock_timestamp();    
    
    raise notice '--do something...,time=%',clock_timestamp();    
    perform pg_sleep(30);
    
    raise notice '--begin to commit,time=%',clock_timestamp();
    commit;
    raise notice '-- commit over,time=%',clock_timestamp();
    
    raise notice 'job is over,time=%',clock_timestamp();
    
    exception when others then
        raise notice 'sqlstate=%,sqlerrm=%', sqlstate,sqlerrm;    
end;
/

残缺的执行后果如下：

postgres=# call proc2();
NOTICE:  --begin to drop table t2,time=2021-12-22 17:57:58.572717+08
NOTICE:  --drop table t2,time=2021-12-22 17:57:58.573627+08
NOTICE:  --begin to alter table t1 rename to t2,time=2021-12-22 17:57:58.57374+08
NOTICE:  --alter table t1 rename to t2,time=2021-12-22 17:57:58.57425+08
NOTICE:  --do something...,time=2021-12-22 17:57:58.574311+08
NOTICE:  --begin to commit,time=2021-12-22 17:58:28.575849+08
NOTICE:  -- commit over,time=2021-12-22 17:58:28.774332+08
NOTICE:  job is over,time=2021-12-22 17:58:28.774389+08

能够看出，openGauss/MogDB 里能够残缺执行。

总结
通过两个试验，咱们比照测试存储过程中 commit 与 exception 的应用。试验一后果统一，试验二后果不统一。

1. 通过试验一咱们理解能够应用 commit 语句立即提交来确保程序块所作的变动对其它客户端可见，并且这是不可撤销的(rollback)。

2. 在 PostgreSQL 外面，咱们不能在有 exception 子句的存储过程应用 commit 或者 rollback 语句，openGauss/MogDB 里则能够兼容这两种操作。

墨天轮原文链接：https://www.modb.pro/db/22016…（复制链接至浏览器或点击文末浏览原文查看)

对于作者
彭冲，云和恩墨 PG 技术顾问，网名“多米爸比”，PG 社区认证专家，中国首期 PostgreSQL ACE Partner，多年从事基于 PostgreSQL 数据库的软件研发，擅长于 PL/PGSQL 业务迁徙及优化，Oracle 到 PostgreSQL 的迁徙降级，异构数据库整合；作为墨天轮 PostgreSQL 实际专栏作者，热衷于 PostgreSQL 实际技术分享，在本人的岗位踊跃推广 PostgreSQL，致力为 PG 社区多做贡献。

正文完
数据库
发表至： 数据库
2022-01-17
 0
关于数据库:北京-Meetup-预告-内含六个话题满满干货期待见面
关于数据库:日均调度-10W-任务实例DolphinScheduler-在蔚来汽车一站式数据治理开发平台的应用改造
关于数据库:足不出户闹元宵挑战-IT-人专属灯谜
关于数据库:滴滴-x-StarRocks极速多维分析创造更大的业务价值