Re: second DML operation fails with updatable cursor

Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> I don't see that in the spec.

> It does say that "if <updatability clause> is not specified, then if
> either INSENSITIVE, SCROLL, or ORDER BY is specified, or if QE is not a
> simply updatable table, then an <updatability clause> of READ ONLY is
> implicit". It also says "If an <updatability clause> of FOR UPDATE with
> or without a <column name list> is specified, then INSENSITIVE shall not
> be specified". But I don't see anything forbidding SCROLL FOR UPDATE
> combination.

SQL92 has this under Leveling Rules:

1) The following restrictions apply for Intermediate SQL:

a) A <declare cursor> shall not specify INSENSITIVE.

b) If an <updatability clause> of FOR UPDATE with or without
a <column name list> is specified, then neither SCROLL nor
ORDER BY shall be specified.

So SCROLL with FOR UPDATE is a Full-SQL-only feature. (In SQL99 it's
broken out as Feature F831-01, but that doesn't tell you much about
how hard it is or whether most implementations have it.)

I don't feel particularly bad about not supporting every such feature.
I think Simon's recommendation is definitely the way to go for 8.3 ---
if anyone is motivated to relax the restriction in the future, they can
figure out how to resolve the corner cases then.

Since we're trying to pull things together for beta2 on Friday, I'll go
make this happen now.

regards, tom lane