Re: [HACKERS] nested-xacts cursors (was Re: Performance with new nested-xacts code)
begin; declare cursor c ...; fetch 1 from c; -- returns tuple 1 begin; fetch 1 from c; -- returns tuple 2 rollback; fetch 1 from c; -- returns tuple 1 again This is mightly ugly but I think it's the most usable of the options seen so far. Imho most usabel would be to handle the cursor like a hold corsor. begin; declare cursor c ...; fetch 1 from c; -- returns tuple 1 begin; fetch 1 from c;-- returns tuple 2 rollback; fetch 1 from c; -- returns tuple 3 For me the reason is, that most likely you are not going to rollback because the fetch did not work or returned something you don't like. Most likely some consequent action did not work out, and the next step will be to correct (or ignore) the problem. You can do that without an extra fetch, because you still have the values in host variables. resetting to tuple 1 imho opens the door for endless loops. Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] nested-xacts cursors (was Re: Performance with new nested-xacts code)
Alvaro Herrera [EMAIL PROTECTED] writes: Yeah. Another answer would be to reset the executor state if the cursor is modified in a subtransaction that aborts: Reset is no solution --- rewinding the cursor to the beginning still leaves it in a state that is inconsistent with the restored state of the bufmgr refcounts, etc. We have to restore the executor tree to the state it was in when we entered the subtransaction, not any earlier or later state. The problem is that that's a *major* bit of work, and probably impossible to do completely (how are you going to get a user-written SRF to restore state?). But I think it would be the best solution if we can think of a reasonable way to do it. Another idea I've been wondering about involves leaving the cursor's state alone at subtrans abort, and instead trying to fix up the bufmgr etc state to be correct for this situation. This seems not real easy since I'm not sure how we distinguish state changes associated with advancing an outer cursor from those associated with completely-inside-the-subxact operations. But it seems at least theoretically doable without breaking user SRFs. Also, it's possible that we could arrange things so that major cost is incurred only when a subxact actually aborts, rather than in the main-line path of control. (Expending lots of cycles at every subxact start to save state that we might never need really sticks in my craw...) One possible plan of attack for this approach is to abandon the notion that bufmgr per se is responsible for figuring out what to reset its state to. Instead we would insist on doing a proper shutdown of inside-the-transaction portals, and expect that doing so would bring the refcounts to where they oughta be. I think that this would have been an unworkably fragile solution back in the day when the present error recovery approach was designed, because there were too many bugs and we were often recovering from the effects of those bugs as much as anything else. But maybe now we could get away with it. BTW, I've been more or less ignoring the nearby debate about whether cursors ought to roll back at subxact abort or not, because right now I don't know how to implement *either* behavior. Unless we have credible theories about how to implement both, it's a bit useless to debate which is better. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] nested-xacts cursors (was Re: Performance with new nested-xacts code)
On Thu, Jul 01, 2004 at 10:19:08AM -0400, Tom Lane wrote: AFAICS we can't allow an inner transaction to use a cursor that was declared in an outer transaction, because if the inner transaction fails then it's not just a matter of the FETCH not rolling back; the subtransaction abort will restore state in the bufmgr and other places that is simply inconsistent with the state of the cursor's plantree. Well! I was reading some code about cursors/portals and it certainly is not an easy issue to handle. begin; begin; declare cursor c ...; end; -- cursor, bufmgr state NOT changed here fetch from c; I tried modifying bufmgr, relcache and catcache to see this (with a simple example) and it works. It seems though that we might have a lot of problems with figuring out which subsystems ought to restore state at subxact commit and which not. AFAICS the only ones that restore state at subxact commit right now are relcache, catcache and bufmgr. I think the three of them should not do so to support this. [...] so the rule would have to be something like cursors can only be touched by the highest subxact nesting level they have ever been visible to. Yuck. Yeah. Another answer would be to reset the executor state if the cursor is modified in a subtransaction that aborts: begin; declare cursor c ...; fetch 1 from c; -- returns tuple 1 begin; fetch 1 from c; -- returns tuple 2 rollback; fetch 1 from c; -- returns tuple 1 again This is mightly ugly but I think it's the most usable of the options seen so far. I'm not sure how hard is to do that -- maybe it's just a matter of running PortalStart() again for the cursor? What do you think? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Hay dos momentos en la vida de un hombre en los que no deberÃa especular: cuando puede permitÃrselo y cuando no puede (Mark Twain) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] nested-xacts cursors (was Re: Performance with new nested-xacts code)
Alvaro Herrera [EMAIL PROTECTED] writes: Well, my opinion is that cursors and other resources should at least be usable from a inner subtransaction in its parent -- because if that can't be done we are wasting some of the benefits, because we can't just stick everything in a subtransaction to be able to retry if it fails. It is a pity that we can't roll back FETCH or lo_close() but at least we can keep them declared/open across a subtransaction commit. AFAICS we can't allow an inner transaction to use a cursor that was declared in an outer transaction, because if the inner transaction fails then it's not just a matter of the FETCH not rolling back; the subtransaction abort will restore state in the bufmgr and other places that is simply inconsistent with the state of the cursor's plantree. If we don't restore bufmgr state at subxact commit, I think that it would work to do begin; begin; declare cursor c ...; end; -- cursor, bufmgr state NOT changed here fetch from c; ... It seems though that we might have a lot of problems with figuring out which subsystems ought to restore state at subxact commit and which not. Another point is that this will NOT work: begin; begin; declare cursor c ...; end; -- cursor, bufmgr state NOT changed here begin; fetch from c; abort; -- oops, wrong state restored here so the rule would have to be something like cursors can only be touched by the highest subxact nesting level they have ever been visible to. Yuck. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
