Cursors and backwards scans and SCROLL

Postgres' implementation of cursors has always had a problem with doing
MOVE or FETCH backwards on complex queries. It works okay for simple
seqscans and indexscans, but fails for plans involving joins,
aggregates, and probably other cases. This happens because the executor
routines for those plan types don't cope with being asked to scan
backwards.

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.

Of course, copying the data is a waste of cycles if the client never
actually asks to scan backwards --- and it could even lead to failures,
i.e. running out of disk space. So I think there needs to be a way to
control whether Materialize gets added or not. The SQL spec has a
provision for this: according to the spec, the client is supposed to say
DECLARE foo SCROLL CURSOR FOR ...
if he intends to do anything except sequential fetches from the cursor.
Postgres presently allows the SCROLL keyword but ignores it. I'd like
to set things up so that Materialize is added only when SCROLL appears
(and the query plan can't handle backwards scan without it).

However, this is going to create backwards-compatibility issues.
We have a few options for what to do:

1. Enforce the SQL spec requirement: error out if backwards fetch is
done when SCROLL wasn't given. But this will surely break a lot
of existing applications that work perfectly well.

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
given). This is efficient and flexible, but it exposes implementation
details to the user, in that whether an error occurs will depend on
which plan the system happens to choose. There are cases where
identical cursor definitions might allow or not allow backwards fetch
depending on the planner's choices. Notice though that errors could
occur only in cases that would silently fail in the present code; so
existing applications that work reliably would not see such errors.

3. Create a runtime parameter (GUC variable) which when set causes us
to assume SCROLL is present even if it's not stated. Setting this
to TRUE would allow existing applications to work without modification;
when it's FALSE, we'd enforce the spec behavior. The trouble with this
is the TRUE setting would likely cause materialization costs to be paid
in very many situations where the client has no intention of fetching
backwards.

I'm presently leaning to #2, even though it exposes implementation
details. I'm open to discussion though. Any preferences? Other ideas?

regards, tom lane