Re: updateable cursors & visibility

Tom Lane writes:

> Serializable or not, there is a good case for saying that cursors don't
> see changes made after they are opened, period.

No one disputes that insensitive cursors are a valid concept. But this
discussion is about updating such a cursor. What view of the data would
such a cursor represent after an update? What about this example:

CREATE TABLE foo (a int PRIMARY KEY);
INSERT INTO foo VALUES (1);
... much later ...
BEGIN;
DECLARE test INSENSITIVE CURSOR FOR UPDATE FOR SELECT a FROM foo;
INSERT INTO foo VALUES (2);
FETCH NEXT FROM test;
UPDATE foo SET a = 2 WHERE CURRENT OF test;
...
COMMIT;

Does the UPDATE catch the constraint violation?

> If we allow the snapshot to change later, what in the world will the
> semantics be? Will we go back to re-scan rows that we previously
> skipped?

Clearly this issue is yucky, that's why they probably invented asensitive
cursors. I wouldn't be surprised to learn that there was some existing
implementation that had updatable insensitive cursors, but the working
group decided it wasn't sound and invented a separate half-way type for
it.

--
Peter Eisentraut peter_e(at)gmx(dot)net