Re: cursors: SCROLL default, error messages

Neil Conway <neilc(at)samurai(dot)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