PostgreSQL 事務(wù)控制及invalid transaction termination研究

IT那活兒發(fā)布于2023-01-11 13:20 / 2102人閱讀

PostgreSQL 事務(wù)控制

在我們做PG遷移的時候，用AWS工具經(jīng)常會遇到這樣的提示“Transactioncontrol is only possible from the top level or nested callinvocations without any other intervening command”

在點一下具體的問題，如上圖所示，發(fā)現(xiàn)是commit的問題。

這里我寫了一個存儲過程，用python程序調(diào)用來測試了一下這個問題。

1.首先創(chuàng)建一個表

create tabletest(a1,a2) as select 1 n,current_timestamp t;

2.接下來創(chuàng)建一個存儲過程

create or replaceprocedure test_insert(a1 int, a2 int) as

begin

for i in a1..a2loop

insert into testvalues (i,current_timestamp);

commit;

end loop;

end;

$$ language plpgsql;

3.在命令行執(zhí)行調(diào)用

存儲過程執(zhí)行成功，插入4條數(shù)據(jù)。

4.接下來我們使用python程序來調(diào)用存儲過程

Python程序代碼如下：

執(zhí)行python

發(fā)現(xiàn)報錯，這里報“invalidtransaction termination”，然后指向存儲過程第5行COMMIT。

5.取消存儲過程中的commit

這一次把commit取消，再次執(zhí)行python

查詢數(shù)據(jù)庫中的數(shù)據(jù)

通過測試可以發(fā)現(xiàn)，在命令行中不管你在存儲過程中加commit或者不加commit，都不會報錯。而通過外部程序調(diào)用的情況，如果你在存儲過程中加了commit，就會報“invalidtransaction termination”

通過搜索，我們發(fā)現(xiàn)官方文檔如下描述。

If CALL isexecuted in a transaction block, then the called procedure cannotexecute transaction control statements. Transaction controlstatements are only allowed if CALL isexecuted in its own transaction.

如果CALL在事務(wù)塊中執(zhí)行，則被調(diào)用的存儲過程無法執(zhí)行事務(wù)控制語句（也就是commit/rollback）等TCL語句。只有CALL在自己事務(wù)中執(zhí)行時，才允許事務(wù)控制語句。而我們使用python程序模塊psycopg連接的時候，通常是以begin開始運行的，這就代表了CALL在事務(wù)塊中運行，是沒辦法在存儲過程中執(zhí)行commit的。

這一點我們可以再證明一下。再次在存儲過程中增加commit語句，然后運行starttransaction。

可以看到命令行也報了ERROR: invalid transaction termination的錯誤。

那么這個問題如何解決呢？

需要我們在應(yīng)用程序進行設(shè)置，增加conn.autocommit= True，這樣就使用了數(shù)據(jù)庫call中本身的事務(wù)，而不是程序在開啟的一層事務(wù)。

還需說明一下，在官方文檔是如下介紹的：

Transactioncontrol is only possible in CALL or DO invocationsfrom the top level or nested CALL or DO invocationswithout any other intervening command. For example, if the call stackis CALLproc1() → CALLproc2() → CALLproc3(),then the second and third procedures can perform transaction controlactions. But if the call stack is CALLproc1() → SELECTfunc2() → CALLproc3(),then the last procedure cannot do transaction control, because ofthe SELECT inbetween.

意思是事務(wù)控制只能在call或者do從頂層進行調(diào)用。在沒有任何其他中間命令的嵌套CALL或DO調(diào)用中也能進行事務(wù)控制。例如，如果調(diào)用棧是CALLproc1() → CALL proc2() → CALLproc3()，那么第二個和第三個過程可以執(zhí)行事務(wù)控制動作。但是如果調(diào)用棧是CALLproc1() → SELECT func2() → CALLproc3()，則最后一個過程不能做事務(wù)控制，因為中間有個SELECT。

以上是我們關(guān)于此類事務(wù)控制問題的一些小研究。