Thread: Re: setFetchSize() [Viruschecked]
Patric Bechtel wrote:
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>On Thu, 17 Jul 2003 13:01:43 -0400, Dmitry Tkach wrote:
>
>Hello Dmitry,
>
>that's exactly what the driver is doing if you do a setFetchSize(10).
>Nevertheless, it won't solve the problem of the server eating HUGE amounts of memory if you do a "declare foo cursor
forselect * from myhugetable". It seems
>like postgresql locks all rows (or even copies them around in memory) until the connection drops again.
>
>
At least one of your two statements above must be wrong :-)
Either it is not the same thing the driver is doing with setFetchSize()
or you are mistaken about the server eating memory and locking rows and
copying them around when you use cursors.
I have never used setFetchSize () (never even seen a driver version
where it was implemented), so, I can't tell you which one is wrong, but
I have been using this cursor approach for almost two years now, in a
huge application, making queries that return tens of millions of rows,
and have never had any locking or memory problems that I could attribute
to a bug in my java code...
Dima
>Patric
>
>
>
>>Rich Cullingford wrote:
>>
>>
>
>
>
>>>All,
>>>Will this function be implemented in Release 7.4? I have several arge
>>>tables that run Java out of memory when I attempt to load them through
>>>JDBC. Any workarounds arppreciated...
>>>
>>>
>
>
>
>>I was using cursors for a while as a workaround...
>>Something like this:
>>
>>
>
>
>
>>c.setAutocommit (false);
>>Statement s = c.createStatement ();
>>s.execute ("declare foo cursor for select * from bar");
>>
>>
>
>
>
>>while (true)
>>{
>> ResultSet rs = s.executeQuery ("fetch 10 from foo");
>> int count = 0;
>> while (rs.next ())
>> {
>> count++;
>> doStuffWithThisRow (rs);
>> }
>>
>> if (count < 10) //The whole cursor contents has been processed
>> break;
>>}
>>
>>
>>I hope, it helps...
>>
>>
>
>
>
>>Dima
>>
>>
>
>
>
>
>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>>
>
>
>
>
>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/AwUBPxbKwXxoBrvMu8qQEQKC8gCfWN0mM5HfiheSggCracN3JPglHckAn0KI
>G5m+Vo0NZQ9l53pE44U9EpyK
>=udRs
>-----END PGP SIGNATURE-----
>
>
>
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 17 Jul 2003 13:20:49 -0400, Dmitry Tkach wrote: Hello Dmitry, >>that's exactly what the driver is doing if you do a setFetchSize(10). >>Nevertheless, it won't solve the problem of the server eating HUGE amounts of memory if you do a "declare foo cursor forselect * from myhugetable". It seems >>like postgresql locks all rows (or even copies them around in memory) until the connection drops again. >> >> >At least one of your two statements above must be wrong :-) >Either it is not the same thing the driver is doing with setFetchSize() >or you are mistaken about the server eating memory and locking rows and >copying them around when you use cursors. > >I have never used setFetchSize () (never even seen a driver version >where it was implemented), so, I can't tell you which one is wrong, but >I have been using this cursor approach for almost two years now, in a >huge application, making queries that return tens of millions of rows, >and have never had any locking or memory problems that I could attribute >to a bug in my java code... That's exactly like I see it here (Windows AND Linux, Version 7.3.3). The setFetchSize-code was analyzed and party improved by me (my patch didn't get accepted, sigh) to be switched on and offby an URL parameter. So I clearly understand what it does. And that's what I told you. But: If I enter into psql and put one of the queries into it with a "declare test cursor for" infront of it, postgres startsincreasing it's memory footprint, and it ends this only if I do a \c anotherDataBase. The same thing I experiencedin my Java apps. I even had problems with the shared memory size, so I had to increase that to unreasonable valuesbeyond 4096 (at least it's unreasonable for ME). The whole database is 300 MB on the harddisk, but some specific queriesstart to hug over 1 GB of memory. That's what I don't understand. And if the query is executed without the cursor,the memory footprint stays within 10 MB! Maybe one of the hackers can explain this... The configuration is standard, only shared buffers is raised, otherwise thequeries tend to break just too soon. 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/AwUBPxbQLHxoBrvMu8qQEQInFACgqBYkm3rcLs2ipqAaEWOGHr8rT+0AoKLa z5e+m9aQf3pFXQRrMm9Agp1f =Z/D8 -----END PGP SIGNATURE-----
Patric Bechtel wrote: >That's exactly like I see it here (Windows AND Linux, Version 7.3.3). >The setFetchSize-code was analyzed and party improved by me (my patch didn't get accepted, sigh) to be switched on and offby an URL parameter. So I clearly understand what it does. And that's what I told you. >But: If I enter into psql and put one of the queries into it with a "declare test cursor for" infront of it, postgres startsincreasing it's memory footprint, and it ends this only if I do a \c anotherDataBase. The same thing I experiencedin my Java apps. I even had problems with the shared memory size, so I had to increase that to unreasonable valuesbeyond 4096 (at least it's unreasonable for ME). The whole database is 300 MB on the harddisk, but some specific queriesstart to hug over 1 GB of memory. That's what I don't understand. And if the query is executed without the cursor,the memory footprint stays within 10 MB! >Maybe one of the hackers can explain this... The configuration is standard, only shared buffers is raised, otherwise thequeries tend to break just too soon. > > I am using cursors in 7.2 (with and without java) all the time, and have never ever seen it do anything like that... Either there is something wrong with your query (e.g., executing a function that leaks memory), or something just got broken in 7.3 having to do with cursors. This has nothing to do with jdbc the way you are describing it. Perhaps, you should bring this up on the pgsql-general, and/or pgsql-bugs lists - if you can come up with a simple test case to reproduce the problem, I am sure, some of the developers there will be willing and able to fix it. Dima >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/AwUBPxbQLHxoBrvMu8qQEQInFACgqBYkm3rcLs2ipqAaEWOGHr8rT+0AoKLa >z5e+m9aQf3pFXQRrMm9Agp1f >=Z/D8 >-----END PGP SIGNATURE----- > > > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > >