Thread: setFetchSize()
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...
Rich Cullingford
rculling@sysd.com
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
Seems to me like a quite good solution! But why this is needed when autocommit is off? I can't understand.
On Thu, 17 Jul 2003 13:01:43 -0400
Dmitry Tkach <dmitry@openratings.com> wrote:
> 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
--
/~\ 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 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.
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-----
Felipe Schnack wrote:
> Seems to me like a quite good solution! But why this is needed when autocommit is off? I can't understand.
>
>
Because you can only have cursors declared inside a transaction :-(
Dima
>On Thu, 17 Jul 2003 13:01:43 -0400
>Dmitry Tkach <dmitry@openratings.com> wrote:
>
>
>
>>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
>>
>>
>
>
>
>
but I said I can't understand it when autocommit is off, not when it's on...
On Thu, 17 Jul 2003 13:16:15 -0400
Dmitry Tkach <dmitry@openratings.com> wrote:
> Felipe Schnack wrote:
>
> > Seems to me like a quite good solution! But why this is needed when autocommit is off? I can't understand.
> >
> >
> Because you can only have cursors declared inside a transaction :-(
>
> Dima
>
> >On Thu, 17 Jul 2003 13:01:43 -0400
> >Dmitry Tkach <dmitry@openratings.com> wrote:
> >
> >
> >
> >>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
> >>
> >>
> >
> >
> >
> >
>
>
--
/~\ 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:
> but I said I can't understand it when autocommit is off, not when it's on...
>
autocommit is off == you are in transaction.
You must be in transaction to be able to declare a cursor.
Dima
>
>On Thu, 17 Jul 2003 13:16:15 -0400
>Dmitry Tkach <dmitry@openratings.com> wrote:
>
>
>
>>Felipe Schnack wrote:
>>
>>
>>
>>> Seems to me like a quite good solution! But why this is needed when autocommit is off? I can't understand.
>>>
>>>
>>>
>>>
>>Because you can only have cursors declared inside a transaction :-(
>>
>>Dima
>>
>>
>>
>>>On Thu, 17 Jul 2003 13:01:43 -0400
>>>Dmitry Tkach <dmitry@openratings.com> wrote:
>>>
>>>
>>>
>>>
>>>
>>>>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
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>>
>
>
>
>
oh, so pgsql is the problem, not the driver?
If I understand what you said well, I just have to turn off autocommit and set fetchsize to any positive number and
thedriver will do its work, right?
On Thu, 17 Jul 2003 19:11:44 +0200
"Patric Bechtel" <bechtel@ipcon.de> 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.
>
> 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-----
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
/~\ 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
Yes, I understand that... that's why I asked why the JDBC driver doesn't automatically use cursos when you have
autocommitoff and a fetch size
On Thu, 17 Jul 2003 13:25:56 -0400
Dmitry Tkach <dmitry@openratings.com> wrote:
> Felipe Schnack wrote:
>
> > but I said I can't understand it when autocommit is off, not when it's on...
> >
> autocommit is off == you are in transaction.
>
> You must be in transaction to be able to declare a cursor.
>
> Dima
>
> >
> >On Thu, 17 Jul 2003 13:16:15 -0400
> >Dmitry Tkach <dmitry@openratings.com> wrote:
> >
> >
> >
> >>Felipe Schnack wrote:
> >>
> >>
> >>
> >>> Seems to me like a quite good solution! But why this is needed when autocommit is off? I can't understand.
> >>>
> >>>
> >>>
> >>>
> >>Because you can only have cursors declared inside a transaction :-(
> >>
> >>Dima
> >>
> >>
> >>
> >>>On Thu, 17 Jul 2003 13:01:43 -0400
> >>>Dmitry Tkach <dmitry@openratings.com> wrote:
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>>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
> >>>>
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>>
> >>
> >>
> >
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@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
Felipe Schnack wrote:
> Yes, I understand that... that's why I asked why the JDBC driver doesn't automatically use cursos when you have
autocommitoff and a fetch size
>
>
Because it was never implemented, I suppose...
Here is what 7.3 code looks like:
public void setFetchSize(int rows) throws SQLException
{
throw org.postgresql.Driver.notImplemented();
}
:-)
Dima
>On Thu, 17 Jul 2003 13:25:56 -0400
>Dmitry Tkach <dmitry@openratings.com> wrote:
>
>
>
>>Felipe Schnack wrote:
>>
>>
>>
>>> but I said I can't understand it when autocommit is off, not when it's on...
>>>
>>>
>>>
>>autocommit is off == you are in transaction.
>>
>>You must be in transaction to be able to declare a cursor.
>>
>>Dima
>>
>>
>>
>>>On Thu, 17 Jul 2003 13:16:15 -0400
>>>Dmitry Tkach <dmitry@openratings.com> wrote:
>>>
>>>
>>>
>>>
>>>
>>>>Felipe Schnack wrote:
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>Seems to me like a quite good solution! But why this is needed when autocommit is off? I can't understand.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>Because you can only have cursors declared inside a transaction :-(
>>>>
>>>>Dima
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>On Thu, 17 Jul 2003 13:01:43 -0400
>>>>>Dmitry Tkach <dmitry@openratings.com> wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>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
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>
>
>
>
>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Thu, 17 Jul 2003 13:41:27 -0400, Dmitry Tkach wrote:
>Felipe Schnack wrote:
>
>> Yes, I understand that... that's why I asked why the JDBC driver doesn't automatically use cursos when you have
autocommitoff and a fetch size
>>
>>
>Because it was never implemented, I suppose...
>Here is what 7.3 code looks like:
>
> public void setFetchSize(int rows) throws SQLException
> {
> throw org.postgresql.Driver.notImplemented();
> }
>
>:-)
>
>Dima
>
Ohoh, I thought we're talking about CVS driver or the prebuilt binary from jdbc.postgresql.org, at least. Please, DON'T
usethe 7.3 original driver, it's full of bugs!
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/AwUBPxbVJnxoBrvMu8qQEQK5RQCgk4ZpJ1CKYD//NW8jHEIzHOxdjDEAoIsp
Mue2hjcsVFFGNsjPjae4LL6U
=5kCL
-----END PGP SIGNATURE-----
This is a multi-part message in MIME format.
Felipe Schnack wrote:
> How are patches being applied to pgsql driver? I would like to implement any new feature to it... but months ago I
senta patch to implement the "DEFAULT" keyword, introduced in pgsql 7.3. The problem is that the last time I checked it
stillwasn't on the official sources... and it wasn't a long time ago.
> This really made me lost the will to help :-(
>
Same here :-(
I have found and fixed a bunch of problems in the driver, sent some of
them to the list, and never had any response ...
The last one I tried is still sitting around in the resent archives
("Array.getArray ()" from 07/09)...
So, I have decided to give up, and stop wasting my time on this. When I
run into problems, I just patch the driver source locally, and use the
fixed version...
Don't see much point in sending the patches to the list anymore :-(
Dima
On Thu, 17 Jul 2003, Dmitry Tkach wrote:
> Felipe Schnack wrote:
>
> > How are patches being applied to pgsql driver? I would like to
> implement any new feature to it... but months ago I sent a patch to
> implement the "DEFAULT" keyword, introduced in pgsql 7.3. The problem
> is that the last time I checked it still wasn't on the official
> sources... and it wasn't a long time ago. > This really made me lost
> the will to help :-( >
I was under the impression that this patch was rejected, not abandoned,
but the archives are down at the moment so I can't check that.
> Same here :-(
> I have found and fixed a bunch of problems in the driver, sent some of
> them to the list, and never had any response ...
> The last one I tried is still sitting around in the resent archives
> ("Array.getArray ()" from 07/09)...
>
> So, I have decided to give up, and stop wasting my time on this. When I
> run into problems, I just patch the driver source locally, and use the
> fixed version...
> Don't see much point in sending the patches to the list anymore :-(
>
Please continue sending them. Dave and Barry, the two maintainers, may
not be the fastest at getting patches in, but they do notice patches and
then get to them when they can. I've rarely seen a patch dropped,
although the delay can be significant.
Even if the patch is not applied the beauty of open source allows other
users to apply it locally as well.
Kris Jurka
> > > How are patches being applied to pgsql driver? I would like to > > implement any new feature to it... but months ago I sent a patch to > > implement the "DEFAULT" keyword, introduced in pgsql 7.3. The problem > > is that the last time I checked it still wasn't on the official > > sources... and it wasn't a long time ago. > This really made me lost > > the will to help :-( > > > I was under the impression that this patch was rejected, not abandoned, > but the archives are down at the moment so I can't check that. Nobody told me anything so I could fix them anyway. > Even if the patch is not applied the beauty of open source allows > other users to apply it locally as well. Well, I have seen other patches being applied... so I hope mine was rejected actually, but I never knew that. -- /~\ 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
This isn't true any more, the backend supports with hold now, so you can
declare a cursor outside a transaction
Dave
On Thu, 2003-07-17 at 13:25, Dmitry Tkach wrote:
> Felipe Schnack wrote:
>
> > but I said I can't understand it when autocommit is off, not when it's on...
> >
> autocommit is off == you are in transaction.
>
> You must be in transaction to be able to declare a cursor.
>
> Dima
>
> >
> >On Thu, 17 Jul 2003 13:16:15 -0400
> >Dmitry Tkach <dmitry@openratings.com> wrote:
> >
> >
> >
> >>Felipe Schnack wrote:
> >>
> >>
> >>
> >>> Seems to me like a quite good solution! But why this is needed when autocommit is off? I can't understand.
> >>>
> >>>
> >>>
> >>>
> >>Because you can only have cursors declared inside a transaction :-(
> >>
> >>Dima
> >>
> >>
> >>
> >>>On Thu, 17 Jul 2003 13:01:43 -0400
> >>>Dmitry Tkach <dmitry@openratings.com> wrote:
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>>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
> >>>>
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>>
> >>
> >>
> >
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
--
Dave Cramer <Dave@micro-automation.net>
Dave Cramer <Dave@micro-automation.net> writes:
> This isn't true any more, the backend supports with hold now, so you can
> declare a cursor outside a transaction
However, if the problem is that the query result is too large to hold in
memory, then a WITH HOLD cursor is a terrible solution. You are simply
moving the problem of coping with the fully-materialized query result
out of the client and into the backend. Admittedly the backend is
probably better able to cope than the client (at least it knows how to
spill rows to disk...), but if you are trying to get good performance
on huge result sets you do not want to use a held cursor, any more than
you want to suck the whole result into client memory.
regards, tom lane
I stand corrected. Dave On Thu, 2003-07-17 at 22:54, Tom Lane wrote: > Dave Cramer <Dave@micro-automation.net> writes: > > This isn't true any more, the backend supports with hold now, so you can > > declare a cursor outside a transaction > > However, if the problem is that the query result is too large to hold in > memory, then a WITH HOLD cursor is a terrible solution. You are simply > moving the problem of coping with the fully-materialized query result > out of the client and into the backend. Admittedly the backend is > probably better able to cope than the client (at least it knows how to > spill rows to disk...), but if you are trying to get good performance > on huge result sets you do not want to use a held cursor, any more than > you want to suck the whole result into client memory. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Dave Cramer <Dave@micro-automation.net>
Tom Lane wrote: >Dave Cramer <Dave@micro-automation.net> writes: > > >>This isn't true any more, the backend supports with hold now, so you can >>declare a cursor outside a transaction >> >> > >However, if the problem is that the query result is too large to hold in >memory, then a WITH HOLD cursor is a terrible solution. You are simply >moving the problem of coping with the fully-materialized query result >out of the client and into the backend. Admittedly the backend is >probably better able to cope than the client (at least it knows how to >spill rows to disk...), but if you are trying to get good performance >on huge result sets you do not want to use a held cursor, any more than >you want to suck the whole result into client memory. > > Aha! That seems to explain those memory problems the other guy (Patric, I think) was complaining about earlier... Dima
This isn't true any more, the backend supports with hold now, so you can
declare a cursor outside a transaction
Dave
On Thu, 2003-07-17 at 13:25, Dmitry Tkach wrote:
> Felipe Schnack wrote:
>
> > but I said I can't understand it when autocommit is off, not when it's on...
> >
> autocommit is off == you are in transaction.
>
> You must be in transaction to be able to declare a cursor.
>
> Dima
>
> >
> >On Thu, 17 Jul 2003 13:16:15 -0400
> >Dmitry Tkach <dmitry@openratings.com> wrote:
> >
> >
> >
> >>Felipe Schnack wrote:
> >>
> >>
> >>
> >>> Seems to me like a quite good solution! But why this is needed when autocommit is off? I can't understand.
> >>>
> >>>
> >>>
> >>>
> >>Because you can only have cursors declared inside a transaction :-(
> >>
> >>Dima
> >>
> >>
> >>
> >>>On Thu, 17 Jul 2003 13:01:43 -0400
> >>>Dmitry Tkach <dmitry@openratings.com> wrote:
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>>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
> >>>>
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>>
> >>
> >>
> >
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>