Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

Scott - I tried to post a SOLVED followup to the JDBC list but it was
rejected :-!

I now have the opposite problem of getting rid of the cursor :-)
ResultSet.close() does not work. I am trying to do a DROP TABLE from the
other Connection, to whack the table I just finished the ETL on, but it just
hangs indefintiely, and pg_locks shows the shared read lock still sitting
there.

I am trying a Statement.close() and Connection.close() now, but I fear I may
have to do something slightly ugly, as I have Apache DBCP sitting in between
me and the actual PG JDBC driver.

I am hoping the slightly ugly thing is only closing the underlying
connection, and does not have to be */etc/init.d/postgresql8.3 restart* :-)
Is there a backdoor way to forcibly get rid of a lock you don't need any
more?

Cheers
Dave

On Mon, Apr 19, 2010 at 1:05 PM, Scott Carey <scott(at)richrelevance(dot)com>wrote:

> On Apr 15, 2010, at 1:01 PM, Dave Crooke wrote:
> > On Thu, Apr 15, 2010 at 2:42 PM, Dave Crooke <dcrooke(at)gmail(dot)com> wrote:
> > Hey folks
> >
> > I am trying to do a full table scan on a large table from Java, using a
> straightforward "select * from foo". I've run into these problems:
> >
> > 1. By default, the PG JDBC driver attempts to suck the entire result set
> into RAM, resulting in java.lang.OutOfMemoryError ... this is not cool, in
> fact I consider it a serious bug (even MySQL gets this right ;-) I am only
> testing with a 9GB result set, but production needs to scale to 200GB or
> more, so throwing hardware at is is not feasible.
> >
>
> For scrolling large result sets you have to do the following to prevent it
> from loading the whole thing into memory:
>
>
> Use forward-only, read-only result scrolling and set the fetch size. Some
> of these may be the default depending on what the connection pool is doing,
> but if set otherwise it may cause the whole result set to load into memory.
> I regularly read several GB result sets with ~10K fetch size batches.
>
> Something like:
> Statement st = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
> java.sql.ResultSet.CONCUR_READ_ONLY)
> st.setFetchSize(FETCH_SIZE);
>

That's what I''m using, albeit without any args to createStatement, and it
now works.

>
>
>
> > 2. I tried using the official taming method, namely
> java.sql.Statement.setFetchSize(1000) and this makes it blow up entirely
> with an error I have no context for, as follows (the number C_10 varies,
> e.g. C_12 last time) ...
> >
> > org.postgresql.util.PSQLException: ERROR: portal "C_10" 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)
> >
> > This is definitely a bug :-)
> >
> >
>
> I have no idea what that is.
>

It was because I was also writing to the same Connection ... when you call
Connection.commit() with the PG JDBC driver, it also kills all your open
cursors.

I think this is a side effect of the PG internal design where it does MVCC
within a table (rows have multiple versions with min and max transaction
ids) ... even a query in PG has a notional virtual transaction ID, whereas
in e.g. Oracle, a query has a start time and visibility horizon, and as long
as you have enough undo tablespace, it has an existence which is totally
independent of any transactions going on around it even on the same JDBC
connection.