Postgres“创建表作为(选择.)”卡住了

Question

我对PostgreSQL11.6使用了带有psycopg2 2.8.6的Python (也在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

在编辑器(pgModeler)中运行查询时，也会产生同样的效果，但在本例中，查询在Idle状态上被卡住了，并创建了目标表。

我不知道哪里出了问题，怎么从这里着手。谢谢!

Answer 1

我在这里回答我自己的问题，以便对其他人有所帮助。

通过将tcp_keepalives_idle Postgres设置从默认的2小时修改到5分钟，解决了这个问题。

Answer 2

问题不是报告的，你得多调查一下。您必须共享有关数据库表、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的"query“列将显示该会话中最后执行的语句。因此，这意味着所有这些会话都使用ROLLBACK语句正确地结束了它们的事务。

如果会话保持在状态idle in transaction中的时间较长，则始终是应用程序未结束事务的应用程序错误。

你可以做两件事：

• 设置idle_in_transaction_session_timeout，以便服务器在一段时间后自动回滚这些事务。这将防止锁被不确定地持有，但您的应用程序将收到错误。
• 修复应用程序，如下所示

.commit()解

我发现重现问题的唯一方法是省略commit操作。

模块psycopg2是Python-compliant，因此默认情况下自动提交功能是关闭的。

如果将此选项设置为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)""")

传统方式

如果不想自动提交事务，则需要在.commit()之后手动调用execute。

Answer 3

只需移除围绕( )的SELECT...

https://www.postgresql.org/docs/11/sql-createtableas.html