无法使用DataGrip或IntelliJ将大量数据加载到PostgreSQL

Question

我使用DataGrip将一些数据从MySQL安装移动到另一个PostgreSQL数据库。

这对于其他3个表格很顺利，但下一个表格有超过50万行，无法导入。

我使用“Copy Table To... (F5)”功能。

这是日志：

16:28 已连接

16:30 用户@localhost：tmp_post 导入到 forum_post：1999 行（1m 58s 206ms）

16:30 无法保存当前事务状态。请检查连接和数据库设置，然后重试。

对于其他错误，例如错误的数据类型、非空列上的空数据，会创建一个非常有用的日志。但现在没有。

当使用基于IntelliJ的IDE的数据库插件时，该问题也很重要，不仅仅是DataGrip。

- tjati

---

可能是Datagrip试图在将数据插入PostgreSQL数据库之前将数据加载到内存中。我曾经尝试使用几个“datapump”软件将大型表从SQL Server复制到PostgreSQL时遇到类似的问题。最终，我不得不使用.NET编写自己的“datapump”应用程序，以非缓冲方式（逐行）读取数据，并使用NpgSQL库的批量复制函数将其插入Postgresql。 - Ed Mendez

你好！我是 DataGrip 的产品营销经理。能否请您将日志发送给 JetBrains？邮箱 [e-mail]。 - moscas

@moscas昨天在最新版本的IntelliJ Ultimate上遇到了同样的问题。有解决方法吗？ - Alexander Suraphel

@moscas正在寻找解决方法，但仍未得到答复。 - Chootti

你有把日志发送给我们吗？ - moscas

Answer 1

最简单的解决方法就是在你的连接字符串中添加"prepareThreshold=0"，如this answer所示：

jdbc:postgresql://ip:port/db_name?prepareThreshold=0

或者，例如，如果您在连接字符串中使用了多个设置：

jdbc:postgresql://hostmaster.com:6432,hostsecond.com:6432/dbName?&targetServerType=master&prepareThreshold=0

这是一个众所周知的问题，当通过PgBouncer连接到PostgreSQL服务器时，而不是IntelliJ本身的问题。当将大量数据加载到数据库中时，IntelliJ将数据分成块并顺序加载它们，每次执行查询并提交数据。默认情况下，PostgreSQL在执行查询5次后开始使用服务器端预准备语句。

使用PreparedStatement API时，默认情况下驱动程序使用服务器端准备语句。为了使用服务器端准备，您需要执行5次查询（可以通过prepareThreshold连接属性进行配置）。内部计数器跟踪语句已被执行的次数，当达到阈值时，它将开始使用服务器端准备语句。

可能您的PgBouncer使用事务池，并且最新版本的PbBouncer不支持带事务池的准备语句。

如何在事务池中使用预处理语句？ 要使预处理语句在此模式下工作，需要PgBouncer在内部跟踪它们，但它并没有这样做。因此，在客户端中禁用预处理语句是继续在此模式下使用PgBouncer的唯一方法。

您可以通过查看IntelliJ 日志文件来验证问题确实是由于与pgbouncer不正确使用预处理语句引起的。为此，请转到帮助->在资源管理器中显示日志，并搜索“org.postgresql.util.PSQLException：ERROR：prepared statement”异常。

2022-04-08 12:32:56,484 [693272684]   WARN - j.database.dbimport.ImportHead - ERROR: prepared statement "S_3649" does not exist 
java.sql.SQLException: ERROR: prepared statement "S_3649" does not exist
  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
  at org.postgresql.jdbc.PgConnection.executeTransactionCommand(PgConnection.java:755)
  at org.postgresql.jdbc.PgConnection.commit(PgConnection.java:777)

- Dmitry.M