Skip site navigation (1) Skip section navigation (2)

SOLVED: Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

From: Dave Crooke <dcrooke(at)gmail(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: SOLVED: Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set
Date: 2010-04-15 23:39:37
Message-ID: j2sca24673e1004151639r3b5e84b5hb757cc736ae5aaa1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
When a connection is used for both reading and writing, a commit() also
destroys any open cursors. Simple workaround - use two connections.

See full discussion on JDBC list.

Cheers
Dave

On Thu, Apr 15, 2010 at 3:01 PM, Dave Crooke <dcrooke(at)gmail(dot)com> wrote:

> I have followed the instructions below to no avail .... any thoughts?
>
>
> http://jdbc.postgresql.org/documentation/83/query.html#query-with-cursor
>
> This is what happens when I reduce the fetch_size to 50 ... stops after
> about 950msec and 120 fetches (6k rows) ....
>
>
> 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)
>
>
> Cheers
> Dave
>
>
>
> 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.
>>
>> 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 :-)
>>
>>
>> Is there a known workaround for this ... will updating to a newer version
>> of the driver fix this?
>>
>> Is there a magic incation of JDBC calls that will tame it?
>>
>> Can I cast the objects to PG specific types and access a hidden API to
>> turn off this behaviour?
>>
>> If the only workaround is to explicitly create a cursor in PG, is there a
>> good example of how to do this from Java?
>>
>> Cheers
>> Dave
>>
>>
>>
>>
>>
>>
>

pgsql-performance by date

Next:From: Віталій ТимчишинDate: 2010-04-16 08:02:06
Subject: Planner not using column limit specified for one column for another column equal to first
Previous:From: Josh BerkusDate: 2010-04-15 22:52:09
Subject: Re: Autovaccum with cost_delay does not complete on one solaris 5.10 machine

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group