本文將分別在openGauss和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

先創建測試表

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

先創建測試表

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里可以完整執行。

總結

通過兩個實驗，我們對比測試存儲過程中commit與exception的使用。實驗一結果一致，實驗二結果不一致。
1.通過實驗一我們了解可以使用commit語句立刻提交來確保程序塊所作的變化對其它客戶端可見，并且這是不可撤銷的(rollback)。
2.在PG里面，我們不能在有exception子句的存儲過程使用commit或者rollback語句，openGauss里則可以兼容這兩種操作。