Thread: HELP: How to tame the 8.3.x JDBC driver with a biq query result set

I have a huge table I need to do a full table scan on.

I have tried it both ways:

1. With vanilla JDBC, the PG driver tries to suck up the entire result set, and crashes the JVM with java.lang.OutOfMemory

2. When I use setFetchSize(), which is both a JDBC standard and the recommendation on the page below ...

http://jdbc.postgresql.org/documentation/83/query.html#query-with-cursor

... it bails after about 900ms / 100 fetches / a few thousand rows, with the following PG internal error:

13:59:55,346 [PerfDataMigrator] FATAL com.hyper9.storage.sample.persistence.PersistenceManager:3064 - hPDM()
13:59:56,054 [PerfDataMigrator] ERROR com.hyper9.storage.sample.persistence.PersistenceManager:3216 - Unexpected error while migrating sample data: 6000
org.postgresql.util.PSQLException: ERROR: portal "C_14" does not exist
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
    at org.postgresql.core.v3.QueryExecutorImpl.fetch(QueryExecutorImpl.java:1527)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.next(AbstractJdbc2ResultSet.java:1843)
    at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:169)
    at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:169)
    at com.hyper9.storage.sample.persistence.PersistenceManager$Migrator.run(PersistenceManager.java:3156)
    at java.lang.Thread.run(Thread.java:619)

Does anyone know of a workaround? Is this bug fixed in a newer release?

Cheers
Dave

On Thu, 15 Apr 2010, Dave Crooke wrote:

> I have a huge table I need to do a full table scan on.
>
> 2. When I use setFetchSize(), which is both a JDBC standard and the
> recommendation on the page below ...
>
> http://jdbc.postgresql.org/documentation/83/query.html#query-with-cursor
>
> ... it bails after about 900ms / 100 fetches / a few thousand rows, with the
> following PG internal error:
>
> org.postgresql.util.PSQLException: ERROR: portal "C_14" does not exist
>

I'm guessing that you have committed the transaction so the portal
(backing the query) gets cleaned up.  To keep a portal open across
transactions you need the WITH HOLD option to DECLARE CURSOR.  There is no
way to do that at the protocol level to work with the current query setup
you have.  If that's what you need, you'll need to rewrite it to DECLARE
CURSOR and then issue the FETCHes yourself.

Kris Jurka

But it's a SELECT, albeit I am doing the write transactions from another statement on the same connection ... if I use two connections, will that fix it?

Cheers
Dave

On Thu, 15 Apr 2010, Dave Crooke wrote:

> But it's a SELECT, albeit I am doing the write transactions from another
> statement on the same connection ... if I use two connections, will that fix
> it?

There is no problem using the same connection for other actions, as long
as you don't end the transaction.  Using another connection will allow you
to control the two transactions separately.

Kris Jurka