Interesting Limit to Inserts with Java

Outsourcer uses Java and in doing so, the INSERT statement uses the Statement Class and the executeUpdate method.  This method can be seen here.

The executeUpdate is used for INSERT, UPDATE, or DELETE and returns the number of rows impacted by the statement as an int.  In Java, an “int” is defined with a maximum of 2,147,483,647.  This means if your SQL statement affects more than 2.1 Billion rows, then Java will throw an error.  Java int documentation here.

A Greenplum customer using Outsourcer recently contacted me about this error message:

Unable to interpret the update count in command completion tag: INSERT 0 3913383319

This is the error message you will get from Java when you try to insert more than 2.1 Billion rows.  In this case, the customer inserted 3.9 Billion rows from Oracle using Outsourcer.

If you hit this limit in Outsourcer, don’t worry.  The rows actually get inserted and all you need to do is ANALYZE the table.  It is an odd limit that can be avoided.  You can use a VIEW in the source to break up the source table into smaller parts or you could use the External Table feature in Outsourcer.  You can also just ignore this error because the data does get inserted.

 

One thought on “Interesting Limit to Inserts with Java

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.