Re: setFetchSize [Viruschecked]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 17 Jul 2003 09:31:45 -0400, Fernando Nasser wrote:

>Felipe Schnack wrote:
>>   There is a way that I can set setFetchSize() that will prevent pgsql from caching all the results of a query to
server'smemory? 
>>   I'm trying to search in the archives but any search I try returns 0 results...
>>

>Have you tried setting it to a positive value and turning off autocommit
>(you must be inside a transaction to do this)?

Hello Felipe,

nice to see that there's another one who has the same problem than me (although if would be nicer if we both wouldn't
;-)).
I've an application using Castor (maybe hibernate, in a later version), but SOME queries tend to produce huge result
sets,as though directly after transferring to  
the 'client' (which in this case is the app server), can be forgotten at all. But postgres seems to hold this query in
oneof the front ends, though. This happens  
in the moment I use setFetchSize() (I've a patched postgres driver which can do this via the URL), the server starts
eatingmemory like nuts. 
In one case, a rather trivial query with very many rows even broke the backend completely, as after 1.5 GB there was no
RAMavailable anymore. It looked like This  
one backend process which grew then is not shrinking anymore (at least no significantly), and I can only get rid of it
byclosing the connection; this one isn't  
trivial, as I'm using a connection pool.
But I need the connection pool, as without this, Castor as well as Hibernate are incredibly slow; for each transaction
theyopen a connection, do what ever is  
needed, commit or rollback, and close the connection. And even IF they wouldn't, it would be a problem, though.
Is there anything one can do to use server side cursors AND having the postgres server using a reasonable amount of
memory?
BTW: I can reproduce this behaviour in psql, too, so it's not really a problem of the JDBC driver itself. But I've
foundno discussion on GENERAL of HACKERS about  
that, too.

tia

Patric

PGP Public Key Fingerprint: 2636 F26E F523 7D62  4377 D206 7C68 06BB

-----BEGIN PGP SIGNATURE-----
Version: PGPsdk version 1.7.1 (C) 1997-1999 Network Associates, Inc. and its affiliated companies.

iQA/AwUBPxagwXxoBrvMu8qQEQJsjwCdE1e3GHFgBGZegw9w0bfxrgjDrPcAn1yV
Xt75aWxAuBqDijvPET2v2v4b
=1OFf
-----END PGP SIGNATURE-----