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

Statement.close() appears to get the job done (in my envrionment, PG's
driver never sees a Connection.close() because of DBCP).

I'd consider the fact that ResultSet.close() does not release the implicit
cursor to be something of a bug, but it may well have been fixed already.

Cheers
Dave

On Mon, Apr 19, 2010 at 6:28 PM, Dave Crooke <dcrooke(at)gmail(dot)com> wrote:

> 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.
>
>
>
>
>