java.lang.NegativeArraySizeException with jdbc/plan

[sstuddard]

While using jdbc/plan on a large read, I'm getting a java.lang.NegativeArraySizeException. A few hits searching indicate it may be because of a > 1GB payload. In this case, no single record is that size. I happen to know the total output of this in CSV amounts to about 14GB.

The operation works fine with a LIMIT on the data.

Unsure if this is an issue with jdbc, next.jdbc, or PostgreSQL.

My dependencies are:

[[org.clojure/clojure "1.10.0"]
 [org.apache.hadoop/hadoop-common "2.2.0"]
 [org.apache.orc/orc-core "1.6.2"]
 [org.postgresql/postgresql "42.2.5"]
 [seancorfield/next.jdbc "1.0.13"]]

The exception:

Exception in thread "main" java.lang.NegativeArraySizeException
    at org.postgresql.core.PGStream.receiveTupleV3(PGStream.java:421)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2146)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:132)
    at next.jdbc.result_set$stmt__GT_result_set.invokeStatic(result_set.clj:509)
    at next.jdbc.result_set$stmt__GT_result_set.invoke(result_set.clj:504)
    at next.jdbc.result_set$reduce_stmt.invokeStatic(result_set.clj:525)
    at next.jdbc.result_set$reduce_stmt.invoke(result_set.clj:516)
    at next.jdbc.result_set$fn$reify__1149.reduce(result_set.clj:608)
    at clojure.core$reduce.invokeStatic(core.clj:6827)
    at clojure.core$reduce.invoke(core.clj:6810)
    at pg2file.core$write_file.invokeStatic(core.clj:105)
    at pg2file.core$write_file.invoke(core.clj:83)
    at pg2file.core$execute_test.invokeStatic(core.clj:125)
    at pg2file.core$execute_test.invoke(core.clj:108)
    at pg2file.core$_main.invokeStatic(core.clj:130)
    at pg2file.core$_main.doInvoke(core.clj:128)
    at clojure.lang.RestFn.applyTo(RestFn.java:137)
    at pg2file.core.main(Unknown Source)

[seancorfield]

Looks like that exception is coming from deep inside PostgreSQL itself: https://github.com/pgjdbc/pgjdbc/blob/REL42.2.5/pgjdbc/src/main/java/org/postgresql/core/PGStream.java#L410-L422

You should be able to stream very large result sets lazily (from the JDBC p.o.v. rather than Clojure's p.o.v.) if you set things up correctly. In general, that means you need auto-commit OFF for your connection and you need to specify the fetch size in your next.jdbc call. See https://cljdoc.org/d/seancorfield/next.jdbc/1.0.13/doc/all-the-options (unfortunately, this is one of those areas that databases vary a lot so PG may need a +ve fetch size or a -ve one or zero and/or auto-commit being turned off or...).

Closing this out, since it isn't a bug in next.jdbc. Feel free to continue to discuss the issue here tho'...

[sstuddard]

Thanks for the pointers Sean! You were spot on. For future me when I google this problem again in a couple years:

• Open connection with {:auto-commit false}
• Use appropriate options with plan, tuning fetch-size to your use case:

  {:fetch-size 4000
  :concurrency :read-only
  :cursors :close
  :result-type :forward-only}

• Be sure to set jvm heap size, I think memory pressure can manifest in the obscure NegativeArraySizeException.

[seancorfield]

Glad you got it working -- and thank you for reporting the solution back here.

[seancorfield]

I've updated the Tips & Tricks section of Friendly SQL Functions to include these options in the PostgreSQL section.