Re: [HACKERS] second DML operation fails with updatable cursor
Tom Lane wrote: > SQL92 has this under Leveling Rules: > > 1) The following restrictions apply for Intermediate SQL: > > a) A shall not specify INSENSITIVE. > > b) If an of FOR UPDATE with or without > a 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.) Oh, ok then. > 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. Ok. Looking at what you committed, I completely misunderstood what you were saying earlier. Yeah, let's leave it like that for now. A nice "not supported" error message is perfectly fine, as long as we can avoid the unexpected behavior. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] second DML operation fails with updatable cursor
Bruce Momjian <[EMAIL PROTECTED]> writes: > I am not adding anything from this thread to the patches_hold queue or > the TODO list, right? We are just going to wait to get reports from the > field? Yeah, I think it's "done until somebody complains". regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] second DML operation fails with updatable cursor
I am not adding anything from this thread to the patches_hold queue or the TODO list, right? We are just going to wait to get reports from the field? --- Tom Lane wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> writes: > > I don't see that in the spec. > > > It does say that "if is not specified, then if > > either INSENSITIVE, SCROLL, or ORDER BY is specified, or if QE is not a > > simply updatable table, then an of READ ONLY is > > implicit". It also says "If an of FOR UPDATE with > > or without a 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 shall not specify INSENSITIVE. > > b) If an of FOR UPDATE with or without > a 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 > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] second DML operation fails with updatable cursor
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > I don't see that in the spec. > It does say that "if is not specified, then if > either INSENSITIVE, SCROLL, or ORDER BY is specified, or if QE is not a > simply updatable table, then an of READ ONLY is > implicit". It also says "If an of FOR UPDATE with > or without a 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 shall not specify INSENSITIVE. b) If an of FOR UPDATE with or without a 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] second DML operation fails with updatable cursor
On Wed, 2007-10-24 at 19:47 +0100, Heikki Linnakangas wrote: > Tom Lane wrote: > > Heikki Linnakangas <[EMAIL PROTECTED]> writes: > >> Yes, re-fetching row you just deleted is supposed to raise an error. > >> That doesn't seem very hard to implement. If an UPDATE/DELETE CURRENT OF > >> doesn't find the tuple to update/delete, raise an error. > > > > Uh, no, the error would have to come from FETCH RELATIVE 0, and there's > > a problem because no single piece of the code has all the facts needed > > to know that an error should be thrown. I don't currently see any > > non-klugy way to detect that. > > No, FETCH RELATIVE 0 is supposed to be a no-op. If the cursor is > positioned "before a row", it's still positioned before a row after > FETCH RELATIVE 0. That's the way I read the spec, anyway. > > But if it's not easy to do, I'm OK with leaving that out. > > > It might make sense to go with Simon's suggestion to just forbid > > non-forwards fetch from a FOR UPDATE cursor (assuming that we agree he's > > read the spec correctly to disallow that). > > I don't see that in the spec. Neither did I; sorry if I implied that. I searched for any evidence that other RDBMS supported such a construct and could find nothing. So hence I say, be strict early, relax later. Otherwise we may have to support some hazy behaviour for a very long time. Plus I never want to hear "we can't do feature X because of the need to support scrollable updateable cursors". -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] second DML operation fails with updatable cursor
Tom Lane wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> writes: >> Yes, re-fetching row you just deleted is supposed to raise an error. >> That doesn't seem very hard to implement. If an UPDATE/DELETE CURRENT OF >> doesn't find the tuple to update/delete, raise an error. > > Uh, no, the error would have to come from FETCH RELATIVE 0, and there's > a problem because no single piece of the code has all the facts needed > to know that an error should be thrown. I don't currently see any > non-klugy way to detect that. No, FETCH RELATIVE 0 is supposed to be a no-op. If the cursor is positioned "before a row", it's still positioned before a row after FETCH RELATIVE 0. That's the way I read the spec, anyway. But if it's not easy to do, I'm OK with leaving that out. > It might make sense to go with Simon's suggestion to just forbid > non-forwards fetch from a FOR UPDATE cursor (assuming that we agree he's > read the spec correctly to disallow that). I don't see that in the spec. It does say that "if is not specified, then if either INSENSITIVE, SCROLL, or ORDER BY is specified, or if QE is not a simply updatable table, then an of READ ONLY is implicit". It also says "If an of FOR UPDATE with or without a is specified, then INSENSITIVE shall not be specified". But I don't see anything forbidding SCROLL FOR UPDATE combination. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] second DML operation fails with updatable cursor
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > Yes, re-fetching row you just deleted is supposed to raise an error. > That doesn't seem very hard to implement. If an UPDATE/DELETE CURRENT OF > doesn't find the tuple to update/delete, raise an error. Uh, no, the error would have to come from FETCH RELATIVE 0, and there's a problem because no single piece of the code has all the facts needed to know that an error should be thrown. I don't currently see any non-klugy way to detect that. It might make sense to go with Simon's suggestion to just forbid non-forwards fetch from a FOR UPDATE cursor (assuming that we agree he's read the spec correctly to disallow that). That would mask the problem cases in a clean way, and we could fix them sometime later as an enhancement, if anyone finds it worthwhile. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] second DML operation fails with updatable cursor
Tom Lane wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> writes: >>> 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. > > AFAICS we cannot really support that without some fairly major revisions > to the way things work --- there's no concept in either the executor or > the cursor-movement stuff of a "hole" within a query's tuple series. > However, the only case that would misbehave is if you try to re-fetch > a row you just deleted, which is a pretty strange thing to do (and > forbidden by spec anyway, I believe) so I think we can leave it as an > unfixed issue for now. The refetch-after-UPDATE case seems important to > fix, though. Yes, re-fetching row you just deleted is supposed to raise an error. That doesn't seem very hard to implement. If an UPDATE/DELETE CURRENT OF doesn't find the tuple to update/delete, raise an error. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] second DML operation fails with updatable cursor
On Wed, 2007-10-24 at 18:29 +0100, Heikki Linnakangas wrote: > >> FETCH RELATIVE 0 re-fetches the current row according to the > manual. > > > > The question is what's the current row, remembering that we've > always > > defined our cursors as INSENSITIVE. > > I tried to find an answer to that in the spec. I'm pretty you're > supposed to see the changes of UPDATEs done through the same cursor, > using WHERE CURRENT OF, even with insensitive cursors. I have no idea > how we could implement that, though. AFAICS in all cases I can find, SCROLLABLE => INSENSITIVE => no UPDATEs. ISTM we should just restrict Updateable cursors to be non-scrollable, plus force non-scrollable if the user hasn't specified scrollability but has specified updateability. That makes sense, since currently we restrict updateable cursors to not be also specified WITH HOLD. If we work out a way that makes sense then we can extend the behaviour in the next release to include scrollable && updateable at the same time. That catches all the problems raised here, I believe. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] second DML operation fails with updatable cursor
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > Our FOR UPDATE cursors aren't exactly INSENSITIVE right now. Yeah, after re-absorbing the code I realized my earlier comment was out of date. I think the true state of affairs is (or should be) that a cursor declared with FOR UPDATE is sensitive and one without is insensitive. >> 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. AFAICS we cannot really support that without some fairly major revisions to the way things work --- there's no concept in either the executor or the cursor-movement stuff of a "hole" within a query's tuple series. However, the only case that would misbehave is if you try to re-fetch a row you just deleted, which is a pretty strange thing to do (and forbidden by spec anyway, I believe) so I think we can leave it as an unfixed issue for now. The refetch-after-UPDATE case seems important to fix, though. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] second DML operation fails with updatable cursor
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
Re: [HACKERS] second DML operation fails with updatable cursor
Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: >>> Tom Lane wrote: While I've not tried this, I think we could fix it by having nodeTidscan use SnapshotAny instead of the query snapshot when fetching a tuple for CurrentOf (but not otherwise, so as to not change the behavior of WHERE tid = ). We'd essentially be taking it on faith that the CurrentOf gave us a TID that was live earlier in the transaction, and so is still safe to fetch. I think everything else would just fall out if the initial heap_fetch weren't rejecting the tuple. > >> I don't like the faith bit. > > Well, don't worry, because it doesn't work anyway. What does seem to > work properly is applying heap_get_latest_tid() to the scan TID obtained > from the cursor. An interesting point from the (draft version of) SQL:2003: After a DELETE WHERE CURRENT OF, the cursor position is "before the next row". An UPDATE WHERE CURRENT OF is supposed raise an exception condition, if the cursor is not positioned on a row. So DELETE WHERE CURRENT OF followed by an UPDATE WHERE CURRENT OF is supposed to throw an error. Another interesting point, from the General Rules section of UPDATE WHERE CURRENT OF: "If, while CR is open, an object row has been marked for deletion by any , marked for deletion by any that identifies any cursor other than CR, updated by any , updated by any , or updated by any that identifies any cursor other than CR, then a completion condition is raised: warning — cursor operation conflict." I don't think it's a big deal if we don't implement those errors and warnings, though. >> FETCH RELATIVE 0 re-fetches the current row according to the manual. > > The question is what's the current row, remembering that we've always > defined our cursors as INSENSITIVE. I tried to find an answer to that in the spec. I'm pretty you're supposed to see the changes of UPDATEs done through the same cursor, using WHERE CURRENT OF, even with insensitive cursors. I have no idea how we could implement that, though. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] second DML operation fails with updatable cursor
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. Another question: if you do DELETE WHERE CURRENT OF, what would you expect to happen to the cursor position? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] second DML operation fails with updatable cursor
Simon Riggs <[EMAIL PROTECTED]> writes: >> Tom Lane wrote: >>> While I've not tried this, I think we could fix it by having nodeTidscan >>> use SnapshotAny instead of the query snapshot when fetching a tuple for >>> CurrentOf (but not otherwise, so as to not change the behavior of WHERE >>> tid = ). We'd essentially be taking it on faith that the >>> CurrentOf gave us a TID that was live earlier in the transaction, and >>> so is still safe to fetch. I think everything else would just fall out >>> if the initial heap_fetch weren't rejecting the tuple. > I don't like the faith bit. Well, don't worry, because it doesn't work anyway. What does seem to work properly is applying heap_get_latest_tid() to the scan TID obtained from the cursor. > FETCH RELATIVE 0 re-fetches the current row according to the manual. The question is what's the current row, remembering that we've always defined our cursors as INSENSITIVE. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] second DML operation fails with updatable cursor
Tom Lane wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> writes: >> That would solve the problem with two updates of the same row, but not this: >> UPDATE .. WHERE CURRENT OF... >> FETCH RELATIVE 0 > >> At the moment, that returns the next row, not the one that was updated. >> Same problem with FETCH NEXT + FETCH PRIOR after the UPDATE. > > Hmm, what I'm seeing is that it returns the original (unmodified) row; > is that what you meant to say? No, that's not what I meant. Here's what I get: postgres=# CREATE TABLE foo (id integer); CREATE TABLE postgres=# INSERT INTO foo SELECT a from generate_series(1,100) a; INSERT 0 100 postgres=# BEGIN; BEGIN postgres=# DECLARE c CURSOR FOR SELECT id FROM foo FOR UPDATE; DECLARE CURSOR postgres=# FETCH 2 FROM c; id 1 2 (2 rows) postgres=# UPDATE foo set ID=20 WHERE CURRENT OF c; UPDATE 1 postgres=# FETCH RELATIVE 0 FROM c; id 3 (1 row) I was expecting to get 20. I do get the original unmodified tuple (2) if I leave out the FOR UPDATE. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] second DML operation fails with updatable cursor
On Wed, 2007-10-24 at 15:50 +0100, Heikki Linnakangas wrote: > Tom Lane wrote: > > "Dharmendra Goyal" <[EMAIL PROTECTED]> writes: > >> If i do update and delete operations on a row pointed by cursor's current > >> then only first operation succeeds, second operation fails. > > > > Hm, by "fails" you mean "does nothing", right? > > > > The reason for this is that WHERE CURRENT OF is implemented as if it > > were WHERE tid = , and historically we've taken that to mean > > the specific tuple at that exact TID. After there's been an update > > already, the tuple at that TID is no longer live to your transaction, > > and so the tid-search fails. To make this work as the spec requires, > > we'd have to be willing to follow the tuple update chain to find the > > currently-live instance of the row. > > > > While I've not tried this, I think we could fix it by having nodeTidscan > > use SnapshotAny instead of the query snapshot when fetching a tuple for > > CurrentOf (but not otherwise, so as to not change the behavior of WHERE > > tid = ). We'd essentially be taking it on faith that the > > CurrentOf gave us a TID that was live earlier in the transaction, and > > so is still safe to fetch. I think everything else would just fall out > > if the initial heap_fetch weren't rejecting the tuple. > > > > Comments anyone? I don't like the faith bit. I'd prefer if we attempted the fetch using the current Snapshot. If that returns an invisible row, then re-fetch at SnapshotAny and follow the chain forwards. That way we're just special casing this situation rather than changing the main line of code. I wonder how serializable transactions are supposed to work in this situation. Can the user really make the transaction throw an error by trying to re-inspect his own changes? Surely not. > That would solve the problem with two updates of the same row, but not this: > UPDATE .. WHERE CURRENT OF... > FETCH RELATIVE 0 Sounds like this problem was a pre-existing issue, but I've not checked. FETCH RELATIVE 0 re-fetches the current row according to the manual. If the current row has been updated then we can only see the new version; the old pre-UPDATE version must not be visible to us, ever. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] second DML operation fails with updatable cursor
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > That would solve the problem with two updates of the same row, but not this: > UPDATE .. WHERE CURRENT OF... > FETCH RELATIVE 0 > At the moment, that returns the next row, not the one that was updated. > Same problem with FETCH NEXT + FETCH PRIOR after the UPDATE. Hmm, what I'm seeing is that it returns the original (unmodified) row; is that what you meant to say? > What does the SQL standard have to say about this? I think it's OK, or at worst an unimplemented feature, since our cursors are always INSENSITIVE. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] second DML operation fails with updatable cursor
Tom Lane wrote: > "Dharmendra Goyal" <[EMAIL PROTECTED]> writes: >> If i do update and delete operations on a row pointed by cursor's current >> then only first operation succeeds, second operation fails. > > Hm, by "fails" you mean "does nothing", right? > > The reason for this is that WHERE CURRENT OF is implemented as if it > were WHERE tid = , and historically we've taken that to mean > the specific tuple at that exact TID. After there's been an update > already, the tuple at that TID is no longer live to your transaction, > and so the tid-search fails. To make this work as the spec requires, > we'd have to be willing to follow the tuple update chain to find the > currently-live instance of the row. > > While I've not tried this, I think we could fix it by having nodeTidscan > use SnapshotAny instead of the query snapshot when fetching a tuple for > CurrentOf (but not otherwise, so as to not change the behavior of WHERE > tid = ). We'd essentially be taking it on faith that the > CurrentOf gave us a TID that was live earlier in the transaction, and > so is still safe to fetch. I think everything else would just fall out > if the initial heap_fetch weren't rejecting the tuple. > > Comments anyone? That would solve the problem with two updates of the same row, but not this: UPDATE .. WHERE CURRENT OF... FETCH RELATIVE 0 At the moment, that returns the next row, not the one that was updated. Same problem with FETCH NEXT + FETCH PRIOR after the UPDATE. What does the SQL standard have to say about this? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] second DML operation fails with updatable cursor
"Dharmendra Goyal" <[EMAIL PROTECTED]> writes: > If i do update and delete operations on a row pointed by cursor's current > then only first operation succeeds, second operation fails. Hm, by "fails" you mean "does nothing", right? The reason for this is that WHERE CURRENT OF is implemented as if it were WHERE tid = , and historically we've taken that to mean the specific tuple at that exact TID. After there's been an update already, the tuple at that TID is no longer live to your transaction, and so the tid-search fails. To make this work as the spec requires, we'd have to be willing to follow the tuple update chain to find the currently-live instance of the row. While I've not tried this, I think we could fix it by having nodeTidscan use SnapshotAny instead of the query snapshot when fetching a tuple for CurrentOf (but not otherwise, so as to not change the behavior of WHERE tid = ). We'd essentially be taking it on faith that the CurrentOf gave us a TID that was live earlier in the transaction, and so is still safe to fetch. I think everything else would just fall out if the initial heap_fetch weren't rejecting the tuple. Comments anyone? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings