在我们做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。
以上是我们关于此类事务控制问题的一些小研究。
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/130157.html
摘要：深入解析系列之并发控制与事务机制并发控制旨在针对数据库中对事务并行的场景，保证中的一致性与隔离。启动并执行第一个命令。事务管理器分配，并返回事务快照，因为正在进行中。意味着该行由另一个并发事务更新，并且其事务尚未终止。 showImg(https://segmentfault.com/img/remote/1460000018081793); 深入解析 PostgreSQL 系列整理...
摘要：深入解析系列之并发控制与事务机制并发控制旨在针对数据库中对事务并行的场景，保证中的一致性与隔离。启动并执行第一个命令。事务管理器分配，并返回事务快照，因为正在进行中。意味着该行由另一个并发事务更新，并且其事务尚未终止。 showImg(https://segmentfault.com/img/remote/1460000018081793); 深入解析 PostgreSQL 系列整理...
摘要：以上出自发行说明，这段指出版本支持自动查杀超过指定时间的空闲事务连接，下面演示下。修改以下参数备注参数单位为毫秒，这里设置超时空闲事务时间为秒。数据库日志备注数据库日志里清晰地记录了进程的连接由于空闲事务超时被断开连接。 熟悉 PostgreSQL 的朋友应该知道 idle in transaction 进程，引发 idle in transaction 的原因很多，例如应用代码中忘记...
摘要：事务原子性以下转账给要么都成功要么失败标识符如果打了双引号整个引号内视为标识符都会被理解为小写比如标识符其实是被解释为同一个但是因此建议是总是打引号中的常量用单引号引用不支持的转义即不能出现字符不代表不能使用四位十六进制六位十六进制如 database cluster: a collection of databases managed by a single PostgreSQL s...
阅读 1034·2023-01-11 13:20
阅读 1309·2023-01-11 13:20
阅读 819·2023-01-11 13:20
阅读 1413·2023-01-11 13:20
阅读 3719·2023-01-11 13:20
阅读 2109·2023-01-11 13:20
阅读 1091·2023-01-11 13:20
阅读 3152·2023-01-11 13:20
