我使用带有psycopg2
2.8.6的Python来对抗Postgresql 11.6(也在11.9上尝试过(
当我运行查询时
CREATE TABLE tbl AS (SELECT (row_number() over())::integer "id", "col" FROM tbl2)
代码被卡住了(cursor.execute
永远不会返回(,用pg_terminate_backend
终止事务会从服务器中删除查询,但代码不会释放。然而,在这种情况下,目标表被创建。
没有任何东西会锁定交易。内部SELECT
查询本身已经过测试,运行良好。
我试着分析服务器上的线索,在pg_stat_activity
中发现了以下内容:
- 事务
state
是idle in transaction
wait_event_type
就是Client
wait_event
就是ClientRead
当我在SQL编辑器(pgModeler(中运行查询时,也会发生同样的效果,但在这种情况下,查询被固定在Idle
状态,并创建了目标表。
我不知道出了什么问题,也不知道如何从这里着手。谢谢
我在这里回答自己的问题,以便对他人有所帮助。
通过将tcp_keepalives_idle
Postgres设置从默认的2小时修改为5分钟,解决了该问题。
问题无法解决,您必须进行更多调查。您必须分享有关数据库表、python代码和服务器操作系统的更多详细信息。
您还可以与我们分享连接到Python的strace
,这样我们就可以看到查询过程中实际发生了什么。
-
wait_event_type=Client:服务器进程正在等待来自用户应用程序的套接字上的一些活动,并且服务器希望发生独立于其内部进程的事情。
wait_event
将识别特定的等待点。 -
wait_event=ClientRead:等待
ClientRead
的会话处理完最后一个查询,并等待客户端发送下一个请求。这样的会话可以阻止任何事情的唯一方法是如果它的状态是idle in transaction
。所有锁都会一直保持到事务结束,而一旦事务结束,就不会保持任何锁。 -
事务中的空闲:活动可以是
idle
(即等待客户端命令(、idle in transaction
(在BEGIN
块内等待客户端(或命令类型名称,如SELECT
。此外,如果服务器进程当前正在等待另一个会话持有的锁,则会附加等待。
问题可能与有关
- 网络问题
- 创建了相同表名的某个未提交事务
- 事务未提交
您指出这不是提交问题,因为SQL编辑器也会这样做,但在您的问题中,您指定编辑器成功创建了表。
在pgModeler中,您可以看到idle
,这意味着会话是空闲的,而不是查询。
如果会话空闲;查询";pg_stat_activity
的列显示该会话中最后执行的语句。因此,这只是意味着所有这些会话都使用ROLLBACK语句正确地结束了它们的事务。
如果会话在状态idle in transaction
中停留的时间更长,那么这始终是应用程序错误,应用程序不会结束事务。
你可以做两件事:
-
设置
idle_in_transaction_session_timeout
,以便服务器在一段时间后自动回滚这些事务。这将防止锁被无限地持有,但您的应用程序将收到一个错误。 -
修复应用程序,如下所示
.commit()
溶液
我发现重现这个问题的唯一方法是省略commit
操作。
模块psycopg2
与Python DB API兼容,因此默认情况下自动提交功能处于关闭状态。
如果此选项设置为False
,则需要调用conn.commit
将任何挂起的事务提交到数据库。
启用自动提交
您可以按如下方式启用自动提交:
import psycopg2
connection = None
try:
connection = psycopg2.connect("dbname='myDB' user='myUser' host='localhost' password='myPassword'")
connection.autocommit = True
except:
print "Connection failed."
if(connection != None):
cursor = connection.cursor()
try:
cursor.execute("""CREATE TABLE tbl AS (SELECT (row_number() over())::integer 'id', 'col' FROM tbl2)""")
except:
print("Failed to create table.")
with
语句
您还可以使用with
语句来自动提交事务:
with connection, connection.cursor() as cursor: # start a transaction and create a cursor
cursor.execute("""CREATE TABLE tbl AS (SELECT (row_number() over())::integer 'id', 'col' FROM tbl2)""")
传统方式
如果您不想自动提交事务,则需要在execute
之后手动调用.commit()
。
只需移除SELECT...
周围的( )
https://www.postgresql.org/docs/11/sql-createtableas.html