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's memory?
>> 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 one of 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 eating memory 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 RAM available 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 by closing 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 they open 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 found no 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-----