4

opengauss/mogdb存储过程事务控制与异常块 -利来国际app

多米爸比 2021-12-22
168

本文将分别在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里则可以兼容这两种操作。

最后修改时间:2022-01-01 10:36:33
「喜欢文章,快来给作者赞赏墨值吧」
【利来手机国际的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:[email protected]进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论