SOLVED ... Re: 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@richrelevance.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@gmail.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.