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


21 Mar 2003 11:35:38 -0500
Neil Conway <neilc@samurai.com>
cursors: SCROLL default, error messages
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

Fri, 21 Mar 2003 12:12:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: cursors: SCROLL default, error messages 
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


21 Mar 2003 12:29:42 -0500
Neil Conway <neilc@samurai.com>
Re: cursors: SCROLL default, error messages
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 supports it
	SCROLL		allow, add overhead if needed to support
The <nothing> case would deviate from the spec but would give backwards
compatibility.

			regards, tom lane

Fri, 21 Mar 2003 12:33:28 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: cursors: SCROLL default, error messages 

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


21 Mar 2003 17:12:33 -0500
Greg Stark <gsstark@mit.edu>
Re: cursors: SCROLL default, error messages
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 specify SCROLL.

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

Fri, 21 Mar 2003 17:38:50 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: cursors: SCROLL default, error messages 
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


22 Mar 2003 20:23:22 -0500
Neil Conway <neilc@samurai.com>
Re: cursors: SCROLL default, error messages
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


Sat, 22 Mar 2003 20:37:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: cursors: SCROLL default, error messages 
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


24 Mar 2003 12:24:27 -0500
Neil Conway <neilc@samurai.com>
Re: cursors: SCROLL default, error messages
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


Mon, 24 Mar 2003 12:28:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: cursors: SCROLL default, error messages 