Re: setFetchSize [Viruschecked]

Felipe Schnack wrote:
> Hi Patric!
> hm... would be nice if this patch of yours were in the current driver... you submitted it to the maintainers?
> I actually don't understand why the default behaviour is to fetch all data, seems crazy to me :-)
>

In 7.4 the protocol that clients use to talk with the server has changed
to allow much more efficient fetching of data. One of the reasons this
was changed was because of feedback from the jdbc driver developers. So
in the future this will work much better than today. Unfortunately it
will take time before all of the new features in the V3 protocol are
utilized by the jdbc code. But it is now possible to do a much better job.

thanks,
--Barry

> On Thu, 17 Jul 2003 16:12:32 +0200
> "Patric Bechtel" <bechtel(at)ipcon(dot)de> wrote:
>
>
>>-----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-----
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>
>
>