Re: updateable cursors & visibility - Mailing list pgsql-hackers

On Mon, 31 Mar 2003, Peter Eisentraut wrote:

> 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?

Good point. There is no direct reference to this condition in SQL99 -- as
far as I can tell. We do have this however in SQL99 14.9 General Rules:

4) The extent to which an SQL-implementation may disallow independent
changes that are not significant is implementation-defined

Where 'independent' means a change not made by <update
statement: positioned> or <delete statement: positioned> and 'significant'
means that, had the change been made before the cursor was opened, the
underlying table of the cursor would be different in some respect. If the
cursor is insensitive, then significant changes are not visible by the
cursor.

So, for insensitive cursors we could disallow changes independent of the
cursor. I don't think this is useful but it suggests that sensitive
cursors should allow independent changes and that these should be visible
to the cursor. But that isn't really the issue.

We also get the following in SQL99 <update statement: positioned> General
Rules:

8) If, while CR is open, an object row has been marked for deletion by any
<delete statement: searched>, marked for deletion by any <delete
statement: positioned> that identifies any cursor other than CR, updated
by any <update statement: searched>, or updated by any <update
statement: positioned> that identifies any cursor other than CR, then a
completion condition is raised: warning  cursor operation conflict.

But this just muddies the waters. I think it comes down to
this: insensitive cursors should behave as they currently do. If they are
used by update/delete statement: positioned, they still need to adhere to
the normal visibility of UPDATE or DELETE -- which is what, I think, Bruce
originally proposed.

So, I would like to go ahead and implement update/delete
statement: positioned (regardless of the nature of visibility we decide
on). Bruce's proposal basically sees the tid of the last FETCH'd or MOVE'd
to tuple stored in the Portal structure for that cursor. This shouldn't be
hard since DoRelativeFetch() calls ExecutorRun() for non-holdable cursors,
which returns the last TupleTableSlot returned, which gives access to the
tid of the last FETCH'd tuple. Likewise, DoRelativeStoreFetch() seems to
have direct access to the data required, covering holdable cursors.

This means that when we handle an update/delete statement: positioned, we
could either do a rough hack and look up the Portal for the named cursor
inside of the parse and analyze code and fill out a where clause Node to
the effect of 'ctid = <saved tid>'.

Alternatively, we could just register that it is a positioned update/delete 
and look it up somewhere else: planner, executor...?

Regardless of which, we could insert a special case in ExecutePlan() (or
somewhere more appropriate?) to test that the tuple returned from the
lower level ExecTidScan() still satisifies the cursor query. It should be
sufficient to use HeapTupleSatisfies() or some of the logic there in to do
this. If all goes well, then the update/delete will succeed.

Comments?

Gavin