Обсуждение: setFetchSize
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... -- /~\ The ASCII Felipe Schnack (felipes@ritterdosreis.br) \ / Ribbon Campaign Analista de Sistemas X Against HTML Cel.: 51-91287530 / \ Email! Linux Counter #281893 Centro Universitário Ritter dos Reis http://www.ritterdosreis.br ritter@ritterdosreis.br Fone: 51-32303341
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)? -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
-----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-----
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 :-) On Thu, 17 Jul 2003 16:12:32 +0200 "Patric Bechtel" <bechtel@ipcon.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'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 inone 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 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 noRAM 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 itby 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 transactionthey 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 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----- > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- /~\ The ASCII Felipe Schnack (felipes@ritterdosreis.br) \ / Ribbon Campaign Analista de Sistemas X Against HTML Cel.: 51-91287530 / \ Email! Linux Counter #281893 Centro Universitário Ritter dos Reis http://www.ritterdosreis.br ritter@ritterdosreis.br Fone: 51-32303341
On 17/07/2003 14:24 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... Not AFAIK. A work-round might be to use limit and offset in your sql. HTH -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
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@ipcon.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'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 inone 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 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 noRAM 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 itby 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 transactionthey 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 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----- >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 6: Have you searched our list archives? >> >> http://archives.postgresql.org > > >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 17 Jul 2003 18:28:06 -0700, Barry Lind wrote: > > >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 Hello Barry, you've saved my day... :-) That's VERY good news. I will take a look at the changes and see if I can already patch the CVScode to comply to the changes. I need this very soon. Anyway, how stable is the current CVS 7.4 version? Is anybody using it already at the moment? Especially the windows version?I've to admit that I've to use the Windows version as my notebook does not run Linux in a usable fashion (ACPI/APMprobs). I may ask this question again on [general]... :-) tnx, Patric bis denne 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/AwUBPxefYHxoBrvMu8qQEQKO5gCeO/YQxr/f3cMggGtTO0/Cj83ugngAoJLm qLhpSLL/1OUI+2elFGgV+owD =7UPd -----END PGP SIGNATURE-----
This is actually pretty nice... but I need a solution for today. Summing it all up, today I have to use cursors, right? Better yet, I think I'll patch my jdbc driver to use cursors when I specify a fetch size :-) On Thu, 17 Jul 2003 18:28:06 -0700 Barry Lind <blind@xythos.com> wrote: > > > 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@ipcon.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'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 resultsets, 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 inone 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 startseating 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 wasno 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 itby 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 transactionthey 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 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----- > >> > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 6: Have you searched our list archives? > >> > >> http://archives.postgresql.org > > > > > > > > > -- /~\ The ASCII Felipe Schnack (felipes@ritterdosreis.br) \ / Ribbon Campaign Analista de Sistemas X Against HTML Cel.: 51-91287530 / \ Email! Linux Counter #281893 Centro Universitário Ritter dos Reis http://www.ritterdosreis.br ritter@ritterdosreis.br Fone: 51-32303341
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Fri, 18 Jul 2003 07:51:34 -0300, Felipe Schnack wrote: > This is actually pretty nice... but I need a solution for today. Summing it all up, today I have to use cursors, right? > Better yet, I think I'll patch my jdbc driver to use cursors when I specify a fetch size :-) > Just use the current CVS version, it already does this. 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/AwUBPxfPPnxoBrvMu8qQEQKy+gCg7JWVlpHV+RQfbBMCMj6wqKpsGXEAoKeE hNAo/I5OloVaVEizL2O272Nt =qo6B -----END PGP SIGNATURE-----