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。
以上是我们关于此类事务控制问题的一些小研究。