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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly