本文将分别在openGauss/MogDB和PostgreSQL数据库中测试存储过程commit与exception的使用。
实验一
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表会发生锁等待(截图中的左下和右下部分)。
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处理，修改后的代码如下
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/docs/current/plpgsql-transactions.html
因此在PG里面，我们不能再有exception子句的存储过程使用commit或者rollback语句。
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.在PG里面，我们不能在有exception子句的存储过程使用commit或者rollback语句，openGauss/MogDB里则可以兼容这两种操作。本文将分别在openGauss/MogDB和PostgreSQL数据库中测试存储过程commit与exception的使用。
实验一
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表会发生锁等待(截图中的左下和右下部分)。
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处理，修改后的代码如下
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/docs/current/plpgsql-transactions.html
因此在PG里面，我们不能再有exception子句的存储过程使用commit或者rollback语句。
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.在PG里面，我们不能在有exception子句的存储过程使用commit或者rollback语句，openGauss/MogDB里则可以兼容这两种操作。
