本文将别离在openGauss/MogDB和PostgreSQL数据库中测试存储过程commit与exception的应用。
试验一
1.PostgreSQL
先创立测试表：
create table t1(id int);
上面创立存储过程proc1：
create or replace procedure proc1() as$$declarebegin 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+08NOTICE: table "t2" does not exist, skippingNOTICE: --drop table t2,time=2021-12-22 17:11:52.747054+08NOTICE: --begin to alter table t1 rename to t2,time=2021-12-22 17:11:52.747057+08NOTICE: --alter table t1 rename to t2,time=2021-12-22 17:11:52.74728+08NOTICE: --do something...,time=2021-12-22 17:11:52.74729+08NOTICE: --begin to commit,time=2021-12-22 17:12:22.778001+08NOTICE: -- commit over,time=2021-12-22 17:12:22.778074+08NOTICE: job is over,time=2021-12-22 17:12:22.778081+08CALLTime: 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() asbegin 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+08NOTICE: table "t2" does not exist, skippingCONTEXT: SQL statement "drop table if exists t2"PL/pgSQL function proc1() line 4 at SQL statementNOTICE: --drop table t2,time=2021-12-22 17:37:40.721364+08NOTICE: --begin to alter table t1 rename to t2,time=2021-12-22 17:37:40.721404+08NOTICE: --alter table t1 rename to t2,time=2021-12-22 17:37:40.721835+08NOTICE: --do something...,time=2021-12-22 17:37:40.721917+08NOTICE: --begin to commit,time=2021-12-22 17:38:10.723386+08NOTICE: -- commit over,time=2021-12-22 17:38:11.072483+08NOTICE: 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$$declarebegin 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+08NOTICE: --drop table t2,time=2021-12-22 17:48:56.031055+08NOTICE: --begin to alter table t1 rename to t2,time=2021-12-22 17:48:56.031082+08NOTICE: --alter table t1 rename to t2,time=2021-12-22 17:48:56.031242+08NOTICE: --do something...,time=2021-12-22 17:48:56.031269+08NOTICE: --begin to commit,time=2021-12-22 17:49:26.09492+08NOTICE: sqlstate=2D000,sqlerrm=cannot commit while a subtransaction is activeCALLTime: 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() asbegin 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+08NOTICE: --drop table t2,time=2021-12-22 17:57:58.573627+08NOTICE: --begin to alter table t1 rename to t2,time=2021-12-22 17:57:58.57374+08NOTICE: --alter table t1 rename to t2,time=2021-12-22 17:57:58.57425+08NOTICE: --do something...,time=2021-12-22 17:57:58.574311+08NOTICE: --begin to commit,time=2021-12-22 17:58:28.575849+08NOTICE: -- commit over,time=2021-12-22 17:58:28.774332+08NOTICE: 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社区多做贡献。