pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedUse Declare/Fetch option
4+ messages / 2 participants
[nested] [flat]
* Use Declare/Fetch option
@ 1999-01-27 10:08 Hiroshi Inoue <Inoue@tpf.co.jp>
0 siblings, 1 reply; 4+ messages in thread
From: Hiroshi Inoue @ 1999-01-27 10:08 UTC (permalink / raw)
To: pgsql-interfaces
Hello all,
I have a question about psqlodbc driver.
Now the default setting for "Use Declare/Fetch" is false.
In MVCC thanks to Vadim,readers block no writers.
So we can change the [Use Declare/Fetch] option to true ?
And if so,it's effective ?
Thanks.
Hiroshi Inoue
Inoue@tpf.co.jp
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] Use Declare/Fetch option
@ 1999-01-27 15:59 Byron Nikolaidis <byronn@insightdist.com>
parent: Hiroshi Inoue <Inoue@tpf.co.jp>
0 siblings, 0 replies; 4+ messages in thread
From: Byron Nikolaidis @ 1999-01-27 15:59 UTC (permalink / raw)
To: Hiroshi Inoue <Inoue@tpf.co.jp>; +Cc: pgsql-interfaces
Hiroshi Inoue wrote:
> Hello all,
>
> I have a question about psqlodbc driver.
> Now the default setting for "Use Declare/Fetch" is false.
>
> In MVCC thanks to Vadim,readers block no writers.
> So we can change the [Use Declare/Fetch] option to true ?
> And if so,it's effective ?
>
All you say is correct. It probably could go back to true. But I
think some people would probably prefer it stay false, because there is
more odbc functionality provided with this setting. For example, when
the option is false, the entire SQLExtendedFetch functionality is
supported (prev,first,last,next,absolute,relative,bookmark). When the
Declare/Fetch option is true, only fetch "next" is supported. There are
more things like this.
If you have extremely large result sets, then setting the option to true
would prevent the driver from sucking down all your memory. And when
the MVCC is available, the problem of locking rows won't be a problem
anymore. For small to medium result sets, setting the option to false
will probably result in faster performance.
A future enhancement I have in mind for the driver is using both memory
and files for caching large result sets and/or using a combination of
both kinds of tuple access.
Byron
> Thanks.
>
> Hiroshi Inoue
> Inoue@tpf.co.jp
^ permalink raw reply [nested|flat] 4+ messages in thread
* [Fwd: [INTERFACES] Use Declare/Fetch option]
@ 1999-01-28 17:03 Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 4+ messages in thread
From: Byron Nikolaidis @ 1999-01-28 17:03 UTC (permalink / raw)
To: pgsql-interfaces@hub.org <pgsql-interfaces@hub.org>
Hiroshi Inoue wrote:
> Hello,
>
> I have a question.
>
> > -----Original Message-----
> > From: Byron Nikolaidis [mailto:byronn@insightdist.com]
> > Sent: Thursday, January 28, 1999 1:00 AM
> > To: Hiroshi Inoue
> > Cc: pgsql-interfaces
> > Subject: Re: [INTERFACES] Use Declare/Fetch option
> >
> >
> >
> >
> > Hiroshi Inoue wrote:
> >
> > > Hello all,
> > >
> > > I have a question about psqlodbc driver.
> > > Now the default setting for "Use Declare/Fetch" is false.
> > >
> > > In MVCC thanks to Vadim,readers block no writers.
> > > So we can change the [Use Declare/Fetch] option to true ?
> > > And if so,it's effective ?
> > >
> >
> > All you say is correct. It probably could go back to true. But I
> > think some people would probably prefer it stay false, because there is
> > more odbc functionality provided with this setting. For example, when
> > the option is false, the entire SQLExtendedFetch functionality is
> > supported (prev,first,last,next,absolute,relative,bookmark). When the
> > Declare/Fetch option is true, only fetch "next" is supported. There are
>
> Is there somthing wrong with the use "fetch backward" to support
> SQL_FETCH_PRIOR ?
>
I don't think the "fetch backward" works well enough to support FETCH_PRIOR.
We have experimented with it, and found it to be inconsistent at best,
especially when near the end of the result set. In fact, if you fetch past
the end of the result set, you can't even back up anyway! So there is no way
(that I know of) to do something like FETCH_LAST, and then do a FETCH_PRIOR,
because you are already in an bad state. There is also no good way of knowing
how many rows are in the result set, which makes it impossible to prevent
going off the end.
The cursor management in the backend needs to improve quite a bit. From what
I understand, it is rather inefficient as well, because when moving around,
the query is actually being re-executed each time. If it does not improve, I
think the driver would be the best place to manage the result set, and as I
said, in the future I would like to combine the two kinds of access into one,
perhaps even using both memory and file caching for large result sets.
Dave Hartwig has tried to fix the cursor management in the backend -- Maybe he
would like to chime in on this too.
Byron
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] Use Declare/Fetch option
@ 1999-06-30 18:51 Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 4+ messages in thread
From: Byron Nikolaidis @ 1999-06-30 18:51 UTC (permalink / raw)
To: pgsql-interfaces
An old posting for JT...
Hiroshi Inoue wrote:
> Hello all,
>
> I have a question about psqlodbc driver.
> Now the default setting for "Use Declare/Fetch" is false.
>
> In MVCC thanks to Vadim,readers block no writers.
> So we can change the [Use Declare/Fetch] option to true ?
> And if so,it's effective ?
>
All you say is correct. It probably could go back to true. But I
think some people would probably prefer it stay false, because there is
more odbc functionality provided with this setting. For example, when
the option is false, the entire SQLExtendedFetch functionality is
supported (prev,first,last,next,absolute,relative,bookmark). When the
Declare/Fetch option is true, only fetch "next" is supported. There are
more things like this.
If you have extremely large result sets, then setting the option to true
would prevent the driver from sucking down all your memory. And when
the MVCC is available, the problem of locking rows won't be a problem
anymore. For small to medium result sets, setting the option to false
will probably result in faster performance.
A future enhancement I have in mind for the driver is using both memory
and files for caching large result sets and/or using a combination of
both kinds of tuple access.
Byron
> Thanks.
>
> Hiroshi Inoue
> Inoue@tpf.co.jp
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-06-30 18:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-27 10:08 Use Declare/Fetch option Hiroshi Inoue <Inoue@tpf.co.jp>
1999-01-27 15:59 ` Byron Nikolaidis <byronn@insightdist.com>
1999-01-28 17:03 [Fwd: [INTERFACES] Use Declare/Fetch option] Byron Nikolaidis <byronn@insightdist.com>
1999-06-30 18:51 Re: [INTERFACES] Use Declare/Fetch option Byron Nikolaidis <byronn@insightdist.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox