postgres“create table as(select…)”卡住了

xoefb8l8 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(1146)

我在用python psycopg2 针对postgresql 11.6的2.8.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 状态，并创建目标表。
我不确定到底出了什么问题，以及如何从这里着手。谢谢！

sql postgresql python psycopg2 pgmodeler

来源：https://stackoverflow.com/questions/63290472/postgres-create-table-as-select-stuck

3条答案

按热度按时间

ibrsph3r1#

这个问题是不可重复的，你得多调查。您必须共享有关数据库表、python代码和服务器操作系统的更多详细信息。
你也可以和我们分享 strace 附加到python上，因此我们可以看到查询过程中实际发生了什么。
wait\u event\u type=client：服务器进程正在等待来自用户应用程序的套接字上的某些活动，并且服务器希望发生独立于其内部进程的事件。 wait_event 将标识特定的等待点。
wait\u event=clientread：等待的会话 ClientRead 处理完最后一个查询并等待客户端发送下一个请求。这样一个会话可以阻止任何东西的唯一方法是如果它的状态是 idle in transaction . 所有锁都会一直保持到事务结束，而事务完成后不会保持任何锁。
事务中空闲：活动可以 idle （即，等待客户端命令）， idle in transaction （等待客户进入 BEGIN 块）或命令类型名称，例如 SELECT . 另外，如果服务器进程当前正在等待另一个会话持有的锁，则会附加waiting。
问题可能与：
网络问题
创建了相同表名的未提交事务。
事务未提交
您指出这不是提交问题，因为sql编辑器也会这样做，但在您的问题中，您指定编辑器成功地创建了表。
在pgmodeler中，您可以看到 idle ，这意味着会话是空闲的，而不是查询。
如果会话空闲，则 pg_stat_activity 显示该会话中最后执行的语句。所以这仅仅意味着所有这些会话都使用rollback语句正确地结束了它们的事务。
如果会话保持状态 idle in transaction 在很长一段时间里，这总是一个应用程序错误，应用程序没有结束事务。
你可以做两件事：
设置 idle_in_transaction_session_timeout 使这些事务在一段时间后由服务器自动回滚。这将防止锁被不确定地持有，但您的应用程序将收到一个错误。
如下图所示修复应用程序

.commit（）解决方案

我发现重现这个问题的唯一方法就是省略 commit 行动。
模块 psycopg2 是pythondbapi兼容的，因此自动提交特性在默认情况下是关闭的。
将此选项设置为 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 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)""")