psycopg2 查询 sql 时报错

[Soulboy]

V 友们，帮忙给点排查意见

问题不是必现的，偶尔就出现一次

问了 AI 给的也是模糊的的回答

连接 pg 数据库用的是 pool = PooledDB()

执行代码如下

    def dbQuery(self, sql):
        conn = pool.connection()
        cur = conn.cursor()
        cur.execute(sql)
        result = cur.fetchall()
        cur.close()
        conn.close()
        return result

报错如下：

could not receive data from server: Software caused connection abort (0x00002745/10053)

Traceback (most recent call last):
  File "aaTestV5.py", line 844, in process
    input_values = self.getInputValuesInfo()
  File "aa_process.py", line 117, in getInputValuesInfo
    order_list = super(tf, self).getInputValuesInfo()
  File "aaTestV5.py", line 555, in getInputValuesInfo
    results_fromno, no_list = self.getFromnoResult()
  File "aa_process.py", line 366, in getFromnoResult
    return self.getFromnoResultInner(False)
  File "aa_process.py", line 320, in getFromnoResultInner
    results_fromno, no_list = super(tf, self).getFromnoResult(True)
  File "aaTestV5.py", line 119, in getFromnoResult
    result_fromno1 = self.dbQuery(query_sql)
  File "aa_process.py", line 391, in dbQuery
    return super(tf, self).dbQuery(sql)
  File "aaTestV5.py", line 823, in dbQuery
    cur.execute(sql)
  File "\python\lib\site-packages\dbutils\steady_db.py", line 598, in tough_method
    result = method(*args, **kwargs)  # try to execute
psycopg2.DatabaseError: could not receive data from server: Software caused connection abort (0x00002745/10053)

[irisdev]

偶尔会不会全表查锁表了，超时时间设长点或者加个 no wait 试试

[Soulboy]

@irisdev 应该不是锁表呢，更倾向于网络层面的问题

[Soulboy]

@irisdev 看 pg 数据库日志没有这条 sql 的记录，说明还没有发到服务端

[Ackvincent]

网络问题就 telnet 一下。

[Soulboy]

@Ackvincent 能 ping 通，公司用的是 windows server 2008 ，没有 telnet 命令😢

[spritecn]

网络连接环境不好 or 连接数没配够, 我们内网开发环境也经常这样,线上就没有任何问题

[Ackvincent]

先确定防火墙放开了对应端口，端口也监听正常。

[bosoncat]

psycopg 利用 libpq 连接数据库，错误来源于 https://github.com/postgres/postgres/blob/afa5c365ec5ad978878e5d26c536d8f865abf1ae/src/interfaces/libpq/win32.c#L126
可以根据错误代码 WSAECONNABORTED [^1] 排查一下 PostgreSQL 所在机器的网络问题

[^1]: https://learn.microsoft.com/en-us/windows/win32/winsock/windows-sockets-error-codes-2#WSAECONNABORTED