Thread: cursors: SCROLL default, error messages
Folks, While doing some other cursor-related work, I noticed the following two issues: (1) Lack of NO SCROLL The SQL spec specifies that you should be able to specify NO SCROLL to DECLARE CURSOR to disallow bidirectional fetching on the cursor. We currently support the SCROLL syntax, but it had no significant effect on the behavior of the cursor. This was pretty easy to fix, so I implemented NO SCROLL. However, the SQL spec says that if neither SCROLL or NO SCROLL is specified, NO SCROLL should be implicit (SQL 2003 draft, 14.1, Syntax #7). This isn't how cursors have traditionally behaved in PostgreSQL -- backward and forward fetches have always been allowed, whether SCROLL was specified or not. Should we change this behavior to be spec compliant, or default to SCROLL if neither is specified? (2) Error handling If a DECLARE CURSOR is executed for a cursor name that already exists, the existing cursor is closed and replaced by the new cursor (a WARNING is issued). Similarly, a FETCH executed on a non-existent cursor yields a WARNING, not an ERROR. These are not good responses, IMHO: they seem illogical, and they conflict with the way that the vast majority of other commands behave. Should we change this? Cheers, Neil
Neil Conway <neilc@samurai.com> writes:
> The SQL spec specifies that you should be able to specify NO SCROLL to
> DECLARE CURSOR to disallow bidirectional fetching on the cursor. We
> currently support the SCROLL syntax, but it had no significant effect on
> the behavior of the cursor. This was pretty easy to fix, so I
> implemented NO SCROLL.
Hm? As of CVS tip, SCROLL most definitely does something. (No problem
here with adding the noise-word option, of course.)
> However, the SQL spec says that if neither SCROLL or NO SCROLL is
> specified, NO SCROLL should be implicit (SQL 2003 draft, 14.1, Syntax
> #7). This isn't how cursors have traditionally behaved in PostgreSQL --
> backward and forward fetches have always been allowed, whether SCROLL
> was specified or not.
> Should we change this behavior to be spec compliant, or default to
> SCROLL if neither is specified?
We already had that discussion. The agreed-to behavior is that we'll
allow backwards fetch in the cases where it has historically worked
(ie, where there's no additional overhead needed to support it) without
requiring SCROLL. Requiring SCROLL in those cases would break many
existing applications, without improving spec compliance in any way that
any but pedants would care about. However, you will need to say SCROLL
in the cases where overhead has to be added to support backwards fetch.
> If a DECLARE CURSOR is executed for a cursor name that already exists,
> the existing cursor is closed and replaced by the new cursor (a WARNING
> is issued). Similarly, a FETCH executed on a non-existent cursor yields
> a WARNING, not an ERROR.
I agree that the second of these is bogus. I'm ambivalent about
changing the first; it's odd but perhaps there are apps out there
that depend on it. Any other opinions out there?
regards, tom lane
On Fri, 2003-03-21 at 12:12, Tom Lane wrote: > Hm? As of CVS tip, SCROLL most definitely does something. Sorry -- I noticed that it doesn't actually effect whether you can do backward fetches on the cursor, which is what I should have said. > (No problem here with adding the noise-word option, of course.) Note that it won't be a noise word: if NO SCROLL is specified, an attempt to do a backward fetch on a non-scrollable cursor will yield an error. > > Should we change this behavior to be spec compliant, or default to > > SCROLL if neither is specified? > > We already had that discussion. Fair enough (I must have been sleeping :-) ). That behavior seems reasonable to me. Cheers, Neil
Neil Conway <neilc@samurai.com> writes:
> On Fri, 2003-03-21 at 12:12, Tom Lane wrote:
>> (No problem here with adding the noise-word option, of course.)
> Note that it won't be a noise word: if NO SCROLL is specified, an
> attempt to do a backward fetch on a non-scrollable cursor will yield an
> error.
Hm. We could imagine a three-way option:NO SCROLL always error if fetch backwards<nothing> allow if plan
supportsitSCROLL allow, add overhead if needed to support
The <nothing> case would deviate from the spec but would give backwards
compatibility.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Neil Conway <neilc@samurai.com> writes: > > On Fri, 2003-03-21 at 12:12, Tom Lane wrote: > >> (No problem here with adding the noise-word option, of course.) > > > Note that it won't be a noise word: if NO SCROLL is specified, an > > attempt to do a backward fetch on a non-scrollable cursor will yield an > > error. Does the spec *require* an error, or merely say that backward fetches aren't required to work? Most specs don't impose many restrictions on what happens if the user does non conformant things. The SQL spec is a bit, er, nonstandard in that respect, but even for it I'm a bit surprised that it would explicitly say that if NO SCROLL is specified that backward fetches have to produce an error. The only possible rationale I could see for the spec imposing that kind of restriction would be a security concern if an middleware layer allocates a NO SCROLL cursor, pages forward, and then allows an upper tier to do unrestricted operations on that cursor trusting the database not to allow scrolling back. -- greg
Greg Stark <gsstark@mit.edu> writes:
Neil Conway <neilc@samurai.com> writes:
> Note that it won't be a noise word: if NO SCROLL is specified, an
> attempt to do a backward fetch on a non-scrollable cursor will yield an
> error.
> Does the spec *require* an error, or merely say that backward fetches aren't
> required to work?
Actually, what it says is that applications aren't allowed to issue
backwards fetches if they didn't say SCROLL. This is one of the Syntax
Rules for FETCH (in SQL92, but I imagine SQL99 is the same):
3) If the implicit or explicit <fetch orientation> is not NEXT, then the <declare cursor> CR shall
specifySCROLL.
AFAICS, our CVS-tip behavior is a reasonable superset of the spec.
We don't have the "NO SCROLL" noiseword (which was not in SQL92 anyway),
but otherwise I'm happy with what's there now.
regards, tom lane
On Fri, 2003-03-21 at 17:38, Tom Lane wrote: > AFAICS, our CVS-tip behavior is a reasonable superset of the spec. > We don't have the "NO SCROLL" noiseword (which was not in SQL92 anyway), > but otherwise I'm happy with what's there now. Yeah, I guess there's no need to actual enforce NO SCROLL -- but IMHO, it makes the most sense to do so. If the user explicitly says that the cursor can't be used for scrolling, allowing scrolling only serves to make things more confusing, IMHO. In other words, who is *actually* going to specify NO SCROLL, and then expect to scroll the cursor? I'd say just about no one, intentionally -- so if that situation occurs, it is probably the result of programmer error, and we should flag it for standards compliance. Cheers, Neil
Neil Conway <neilc@samurai.com> writes:
> In other words, who is *actually* going to specify NO SCROLL, and then
> expect to scroll the cursor? I'd say just about no one, intentionally --
Well, if you want to do a three-way switch as per earlier discussion,
I won't object. I can't get real excited about it though.
regards, tom lane
On Fri, 2003-03-21 at 12:12, Tom Lane wrote:
> I agree that the second of these is bogus. I'm ambivalent about
> changing the first; it's odd but perhaps there are apps out there
> that depend on it. Any other opinions out there?
For what it's worth, I noticed that the first (DECLARE CURSOR replacing
existing cursors with the same name) doesn't seem to be allowed by the
SQL spec:
(Section 14.1, Syntax Rules)
1. If a <declare cursor> is contained in an SQL-client module M, then:
(a) The <cursor name> shall not be equivalent to the <cursor name> of any other <declare cursor> in M.
Personally, I'm inclined to change both of these cases to result in an
error...
Cheers,
Neil
Neil Conway <neilc@samurai.com> writes:
> Personally, I'm inclined to change both of these cases to result in an
> error...
No strong objection from me, but perhaps you ought to toss out a query
on pgsql-sql or pgsql-general to see if anyone wants to complain. Not
all the folks who might be upset read pghackers.
regards, tom lane