Can not load large amounts of data with DataGrip or IntelliJ to PostgreSQL

Question

I use datagrip to move some data from a mysql installation to another postresql-database.

That worked for 3 other tables like a charm. The next one, over 500.000 rows big, could not be imported.

I use the function "Copy Table To... (F5)".

This is the log.

16:28 Connected

16:30 user@localhost: tmp_post imported to forum_post: 1999 rows (1m 58s 206ms)

16:30 Can't save current transaction state. Check connection and database settings and try again.

For other errors like wrong data types, null data on not null columns, a very helpful log is created. But not now.

The problem is also relevant when using the database plugin for IntelliJ-based IDEs, not only DataGrip

Answer 1

The simplest way to solve the issue is just to add "prepareThreshold=0" to your connection string as in this answer:

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

Or, for example, if you a using several settings in the connection string:

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

It's a well-known problem when connecting to the PostgreSQL server via PgBouncer rather than a problem with IntelliJ itself. When loading massive data to the database IntelliJ splits data into chunks and loads them sequentially, each time executing the query and committing the data. By default, PostgreSQL starts using server-side prepared statements after 5 execution of a query.

The driver uses server side prepared statements by default when PreparedStatement API is used. In order to get to server-side prepare, you need to execute the query 5 times (that can be configured via prepareThreshold connection property). An internal counter keeps track of how many times the statement has been executed and when it reaches the threshold it will start to use server side prepared statements.

Probably your PgBouncer runs with transaction pooling and the latest version of PbBouncer doesn't support prepared statements with transaction pooling.

How to use prepared statements with transaction pooling?

To make prepared statements work in this mode would need PgBouncer to keep track of them internally, which it does not do. So the only way to keep using PgBouncer in this mode is to disable prepared statements in the client

You can verify that the issue is indeed because of the incorrect use of prepared statements with the pgbouncer via viewing IntelliJ log files. For that go to Help -> Show Log in Explorer, and search for "org.postgresql.util.PSQLException: ERROR: prepared statement" exception.

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)