Data Pumper Error copying table

[Jeroen Van Heyningen]

Hi all,

I am relatively new working with SQL Workbench so excuse me if the answer to my problem is obvious.

I am trying to copy a table from one database to another using the data pumper. Everything seems to be set correctly and once I start the data pumping I see in my task manager that the workbench is using the network followed by using some RAM memory but then it suddenly stops working. The workbench log file shows the following:

2018-11-01 16:43:08 INFO  Creating table using sql: CREATE TABLE earth.loww_a320_201707_201801_enriched
(
LIST OF COLUMNS
)
2018-11-01 16:43:09 INFO  Statement for insert: INSERT INTO earth.loww_a320_201707_201801_enriched (LIST OF COLUMNS) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
2018-11-01 16:43:09 INFO  Starting import for table earth.loww_a320_201707_201801_enriched
2018-11-01 16:43:56 ERROR Error releasing savepoint A result was returned when none was expected. [SQL State=0100E]
org.postgresql.util.PSQLException: A result was returned when none was expected.
    at org.postgresql.jdbc.PgConnection.execSQLUpdate(PgConnection.java:442)
    at org.postgresql.jdbc.PgConnection.rollback(PgConnection.java:1658)
    at workbench.db.WbConnection.rollback(WbConnection.java:790)
    at workbench.db.datacopy.QueryCopySource.start(QueryCopySource.java:178)
    at workbench.db.importer.DataImporter.startImport(DataImporter.java:764)
    at workbench.db.datacopy.DataCopier.startCopy(DataCopier.java:590)
    at workbench.db.datacopy.DataCopier$1.run(DataCopier.java:561)

Trying to copy another table with less rows (110.000 vs 690.000 for the one I try to copy) works perfectly but I just can't get this table to be copied.

Anyone an idea of what might be wrong? Is it something with the table itself?

Thanks in advance,

Jeroen

[Thomas Kellerer]

This looks really strange. The error message does not really match the code that is being executed.

This might be caused by excessive RAM usage indeed.

That is probably caused by the JDBC driver's default behaviour to buffer the complete result in memory as described here:

https://www.sql-workbench.eu/manual/dbms-problems.html#pg-problems

The section titled "WbExport or WbCopy using a lot of memory".

Thomas

[Jeroen Van Heyningen]

Dear Thomas,

Thanks a lot for your feedback, that indeed did the trick! I already disabled the autocommit in my connection window but adding a default fetch size was necessary. With 16 GB RAM and the table consisting of 635000 lines and 36 columns I used a fetch size in the connection window of 10 and commit and batch size in the data pumper of 1000. It says in the manual that: "A recommended value is e.g. 10, it might be that higher numbers give a better performance" for the fetch size but when I tried to increase the fetch size, it still failed on memory even with a lower commit/batch size:

2018-11-05 17:33:43 ERROR Error when copying data Not enough memory!
java.sql.SQLException: Not enough memory!
    at workbench.db.importer.DataImporter.processRow(DataImporter.java:1199)
    at workbench.db.datacopy.QueryCopySource.start(QueryCopySource.java:152)

    at workbench.db.importer.DataImporter.startImport(DataImporter.java:764)
    at workbench.db.datacopy.DataCopier.startCopy(DataCopier.java:590)
    at workbench.db.datacopy.DataCopier$1.run(DataCopier.java:561)

So I guess 10 truly is the proper fetch size.

Again, thanks a lot!

Jeroen

Op vr 2 nov. 2018 om 07:54 schreef Thomas Kellerer <google...@sql-workbench.net>:

--
You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Thomas Kellerer]

All rows defined by the fetch size are kept in memory.

If you can't go higher than a fetch size of 10, this means that your rows are either extremely wide (=many columns with e.g. a lot of text content) or that you have a few very large columns (e.g. text, json, or xml) in those tables.

E.g.: if you have a XML column with an average size of 50MB (for each row), then keeping 1000 rows in memory would require at least 50GB of memory (probably more, as the internal representation is a bit bigger).

Jeroen Van Heyningen schrieb am 05.11.2018 um 17:45:
> Dear Thomas,
>
> Thanks a lot for your feedback, that indeed did the trick! I already disabled the autocommit in my connection window but adding a default fetch size was necessary. With 16 GB RAM and the table consisting of 635000 lines and 36 columns I used a fetch size in the connection window of 10 and commit and batch size in the data pumper of 1000. It says in the manual that: "A recommended value is e.g. 10, it might be that higher numbers give a better performance" for the fetch size but when I tried to increase the fetch size, it still failed on memory even with a lower commit/batch size:
>
> 2018-11-05 17:33:43 ERROR Error when copying data Not enough memory!
> java.sql.SQLException: Not enough memory!
>     at workbench.db.importer.DataImporter.processRow(DataImporter.java:1199)
>     at workbench.db.datacopy.QueryCopySource.start(QueryCopySource.java:152)
>     at workbench.db.importer.DataImporter.startImport(DataImporter.java:764)
>     at workbench.db.datacopy.DataCopier.startCopy(DataCopier.java:590)
>     at workbench.db.datacopy.DataCopier$1.run(DataCopier.java:561)
>
> So I guess 10 truly is the proper fetch size.
>
> Again, thanks a lot!
>
> Jeroen
>

> Op vr 2 nov. 2018 om 07:54 schreef Thomas Kellerer <google...@sql-workbench.net <mailto:google...@sql-workbench.net>>:

> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbench%2Bunsu...@googlegroups.com>.

> For more options, visit https://groups.google.com/d/optout.
>

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.

> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.