Re: [SQL] Cursors and backwards scans and SCROLL

Tom,

> Postgres' implementation of cursors has always had a problem with doing
> MOVE or FETCH backwards on complex queries.

Coincidnetally enough, I was just chatting with one of my contractors
yesterday about how the one thing that Transact-SQL has to offer is a really
good cursor implementation. It would be lovely to improve ours to match.

> Fixing this directly seems unreasonably difficult, so I'm currently
> working on fixing it by inserting a Materialize plan node at the top of
> the plan tree for a cursor, if the plan tree couldn't otherwise support
> backwards scan. The Materialize node will save aside a copy of each row
> as it's fetched from the underlying plan, and use this copied table if
> any backwards scanning is asked for.

Sounds good to me. It's also very similar to what T-SQL does for a STATIC or
KEYSET cursor, and works very well in their implementation. (FWIW, T-SQL's
cursor types, such as DYNAMIC and KEYSET, are unnecessary for Postgres due to
MVCC)

> 2. Error out only if a backwards fetch is actually attempted on a plan
> tree that can't handle it (which could only happen if SCROLL wasn't
<snip>
> I'm presently leaning to #2, even though it exposes implementation
> details. I'm open to discussion though. Any preferences? Other ideas?

This sounds like a good idea to me in a staggered-implementation sense if it's
doable. That is, we'd implement the behavior in #2 in the next version of
Postgresql, and the behavior in #1 or in #3 in the version after that. If,
however, the implementation of #2 is too difficult, then I think #3 would be
a good choice.

From my perspective, the "SCROLL" declaration has *always* been the SQL-spec,
and it is the behaviour used by other databases, even if it's been superflous
in PostgreSQL until now. So from that point of view, developers who have
been not using "SCROLL" have been sloppy and can reasonably expect to have to
audit their code in future versions of PostgreSQL.

On the other hand, I don't use cursors much in Postgres, so I'm kind of a
priest doing marriage counselling as far as that's concerned. PL/pgSQL's
"FOR record IN query" is currently both easier and faster than cursors so I
use that 90% of the time.

--
Josh Berkus
Aglio Database Solutions
San Francisco