I am trying to insert 40,000 records in a table in a single transaction using Hibernate (with Envers plugin for auditing). I am suing Postgres driver from Java application. I get the above error after 1,850 rows are inserted. Envers adds a pre-execute hook in Hibernate to load the existing records for auditing purposes. Hence, every insert query has a select query prior to this. This particular run failed on the select query with the error SQL Error: 0, SQLState: XX000 and the message "Caused by: org.postgresql.util.PSQLException: ERROR: transaction is too large to complete; try splitting into pieces
Other times, I have seen the failure on insert query with the error, 40001 for retry.
Is there a way to increase the transaction buffer? I have inserted lot of records with Postgres in a single transaction. For this test, I am using a 2-node CockroachDB cluster with both nodes running on the same machine as the application.