[postgis-users] JDBC OutofMemoryError

[Mark Cave-Ayland]

Hi Akiro,
 
Issuing a SELECT query in PostgreSQL will always cause the complete result
set to be loaded into memory, so if you need a few records at a time then
you must use a cursor. However, it may be that the PostgreSQL JDBC driver
implicity creates cursors when accessing a data source - you'd need to ask
the PostgreSQL people about this as I don't have enough experience in this
area to know if this is the case or not.
 
I think the first thing you need to do is work out whether this is a
PostgreSQL issue or a PostGIS issue. Firstly, make sure that you have got
the latest versions of the PostgreSQL JDBC driver
(http://jdbc.postgresql.org) and PostGIS JDBC sources
(http://postgis.refractions.net). At the time of writing, this is 8.0 build
312 for PostgreSQL and the JDBC2 drivers from postgis 1.0.2. Also, it would
be useful to find out what version of PostgreSQL and PostGIS you are using -
this can be found by doing SELECT version() and SELECT
postgis_full_version().
 
If the error still occurs with these drivers, then try and determine whether
or not the same error occurs if you drop the geometry column from both
tables. If your query works without the geometry columns then it's likely a
PostGIS issue so you'll need to let us have a test case in order to fix it.
If it still fails without geometry columns, then it is likely to be a
PostgreSQL error. In this case, I would recommend that you subscribe to
pgsql-jdbc list on www.postgresql.org and point your questions to the
PostgreSQL developers there.
 
 
Kind regards,
 
Mark.

------------------------
WebBased Ltd
17 Research Way
Tamar Science Park
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023
W: http://www.webbased.co.uk <http://www.webbased.co.uk/> 
  

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Akira
Komo
Sent: 04 August 2005 18:26
To: PostGIS Users Discussion
Subject: RE: [postgis-users] JDBC OutofMemoryError



Mark,

Thank you so much for your replying. The real query would be more
complicated with some join condition. I suspect the problem was from
postgresql JDBC driver. You know a JDBC driver has a buffer at client side,
it should not fetch all the records from the server side after issuing a
query, otherwise you are not able to query a database with million records.
It should load the records dynamically when end users go through the
ResultSet by calling ResultSet.next(). In my case, probably at very
beginning, only serveral hundreds records were downloaded to the JDBC client
side. Notice that every record is 1. The OutOfMemoryError occured just after
executing the statement.executeQuery() before I call Result.next().  

Best Regards

-- Akiro

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050805/bbe12434/attachment.html>