PostgreSQL 15中plpython3u扩展安装成功但函数执行失败求助

已在PostgreSQL 15中成功安装plpython3u扩展，配置了系统及用户级Python环境变量，同时按EnterpriseDB语言包文档要求设置了对应环境变量。创建的plpython3u函数如下：

CREATE EXTENSION IF NOT EXISTS plpython3u;

CREATE OR REPLACE FUNCTION saudacao_simples()
RETURNS TEXT AS $$
  mensagem = 'Olá, mundo!'
  return mensagem
$$ LANGUAGE plpython3u;

执行该函数时触发连接重置错误，报错栈信息如下：

java.net.SocketException: Connection reset
An I/O error occurred while sending to the backend.
at java.base/sun.nio.ch.NioSocketImpl.implRead(Unknown Source)
at java.base/sun.nio.ch.NioSocketImpl.read(Unknown Source)
at java.base/sun.nio.ch.NioSocketImpl$1.read(Unknown Source)
at java.base/java.net.Socket$SocketInputStream.read(Unknown Source)
at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161)
at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:128)
at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:113)
at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
at org.postgresql.core.PGStream.receiveChar(PGStream.java:453)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2120)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:295)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:290)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:600)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$2(SQLQueryJob.java:503)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:510)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:962)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4130)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5148)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

已尝试以下操作但问题未解决：

• 重装plpython3u扩展
• 重启PostgreSQL服务
• 重建数据库连接

排查方向 #

1. Python版本兼容性

   • 执行SELECT * FROM pg_pltemplate WHERE tmplname = 'plpython3u';查看扩展绑定的Python版本，确认与系统安装的Python 3.x版本完全匹配，plpython3u要求编译PostgreSQL时使用的Python版本与运行时一致。

2. PostgreSQL服务器日志分析

   • 查看PostgreSQL数据目录下log文件夹中的服务器日志，连接重置通常伴随PostgreSQL子进程崩溃，日志中会记录Python模块加载失败、内存异常等具体错误原因。

3. 环境变量有效性

   • 确认PostgreSQL服务进程能正确读取PYTHONPATH、PYTHONHOME等环境变量，Windows环境下需确保服务使用的用户账户有权限访问这些变量，可尝试在服务启动脚本中显式配置。

4. 客户端兼容性验证

   • 使用psql客户端直接执行函数，排除DBeaver的JDBC驱动或连接配置问题。若psql执行正常，可尝试更新DBeaver的PostgreSQL驱动或调整连接参数（如关闭SSL、延长超时时间）。

5. 权限检查

   • 确认PostgreSQL运行用户对Python安装目录及依赖库有读取权限，Windows下需检查Python路径是否存在权限限制。

6. 扩展完整性验证

   • 通过pg_config --sharedir找到扩展安装目录，检查plpython3u对应的.dll（Windows）或.so（Linux）文件是否存在且未损坏。

内容的提问来源于stack exchange，提问作者Guilherme Rodrigues