PostgreSQL 事務控制
在我們做PG遷移的時候，用AWS工具經常會遇到這樣的提示“Transactioncontrol is only possible from the top level or nested callinvocations without any other intervening command”
在點一下具體的問題，如上圖所示，發現是commit的問題。
這裡我寫了一個存儲過程，用python程式調用來測試了一下這個問題。
1.首先建立一個表
create tabletest(a1,a2) as select 1 n,current_timestamp t;
2.接下來建立一個存儲過程
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.在指令行執行調用
存儲過程執行成功，插入4條資料。
4.接下來我們使用python程式來調用存儲過程
Python程式代碼如下：
執行python
發現報錯，這裡報“invalidtransaction termination”，然後指向存儲過程第5行COMMIT。
5.取消存儲過程中的commit
這一次把commit取消，再次執行python
查詢資料庫中的資料
通過測試可以發現，在指令行中不管你在存儲過程中加commit或者不加commit，都不會報錯。而通過外部程式調用的情況，如果你在存儲過程中加了commit，就會報“invalidtransaction termination”
通過搜尋，我們發現官方文檔如下描述。
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在事務塊中執行，則被調用的存儲過程無法執行事務控制語句（也就是commit/rollback）等TCL語句。隻有CALL在自己事務中執行時，才允許事務控制語句。而我們使用python程式子產品psycopg連接配接的時候，通常是以begin開始運作的，這就代表了CALL在事務塊中運作，是沒辦法在存儲過程中執行commit的。
這一點我們可以再證明一下。再次在存儲過程中增加commit語句，然後運作starttransaction。
可以看到指令行也報了ERROR: invalid transaction termination的錯誤。
那麼這個問題如何解決呢？
需要我們在應用程式進行設定，增加conn.autocommit= True，這樣就使用了資料庫call中本身的事務，而不是程式在開啟的一層事務。
還需說明一下，在官方文檔是如下介紹的：
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.
意思是事務控制隻能在call或者do從頂層進行調用。在沒有任何其他中間指令的嵌套CALL或DO調用中也能進行事務控制。例如，如果調用棧是CALLproc1() → CALL proc2() → CALLproc3()，那麼第二個和第三個過程可以執行事務控制動作。但是如果調用棧是CALLproc1() → SELECT func2() → CALLproc3()，則最後一個過程不能做事務控制，因為中間有個SELECT。
以上是我們關于此類事務控制問題的一些小研究。