Tom Lane wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> writes: >> Tom Lane wrote: >>> Hmm, what I'm seeing is that it returns the original (unmodified) row; >>> is that what you meant to say? > >> I do get the original unmodified tuple (2) if I leave out the FOR UPDATE. > > Ah, I was testing without FOR UPDATE. I traced through it and the > problem seems to be where ExecutePlan tries to do heap_lock_tuple > during the re-fetch of the row. heap_lock_tuple quite correctly > reports "HeapTupleSelfUpdated" and ExecutePlan just punts: > > case HeapTupleSelfUpdated: > /* treat it as deleted; do not process */ > goto lnext; > > I wonder if it's sane to have this case chase forward to the newest > row version and lock that. Offhand, seeing that FOR UPDATE is supposed > to always return the newest row version, that seems self-consistent; > but I wonder what behaviors it might break.
Yes. I suppose it would change the behavior of "SELECT *, volatilefunc() FROM foo", where volatilefunc would update rows in foo. Doesn't seem like very well-defined behavior anyway. Our FOR UPDATE cursors aren't exactly INSENSITIVE right now. For example: postgres=# truncate foo; INSERT INTO foo SELECT a from generate_series(1,5) a; TRUNCATE TABLE INSERT 0 5 postgres=# BEGIN; DECLARE c CURSOR FOR SELECT id FROM foo FOR UPDATE; BEGIN DECLARE CURSOR postgres=# FETCH RELATIVE 1 FROM c; id ---- 1 (1 row) postgres=# UPDATE foo set id=20 WHERE id = 2; UPDATE 1 postgres=# FETCH RELATIVE 1 FROM c; id ---- 3 (1 row) postgres=# FETCH RELATIVE 1 FROM c; id ---- 4 (1 row) postgres=# FETCH RELATIVE 1 FROM c; id ---- 5 (1 row) postgres=# FETCH RELATIVE 1 FROM c; id ---- (0 rows) Updating a row makes that row disappear from the cursor. But that's actually ok. According to the spec, a cursor is "ASENSITIVE" by default, which basically means the behavior is implementation-dependent. Our read-only cursors are INSENSITIVE, because of MVCC. To summarize the above random thoughts, I think if you change that case in ExecutePlan, we'd have pretty sane behavior: - read-only cursors (no FOR UPDATE or FOR SHARE) would be INSENSITIVE. - FOR UPDATE and FOR SHARE cursors would see any updates or deletes performed in the same transaction. Updated tuples would appear in the position of the original tuple. Inserted tuples wouldn't be visible. Neither would deleted tuples. > Another question: if you do DELETE WHERE CURRENT OF, what would you > expect to happen to the cursor position? According to the spec: before the next row. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 1: 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