Re: Portals and nested transactions

Tom,

As much as I can understand the arguments -- many of them performance-oriented
-- for handling Portals non-transactionally, I simply don't see how we can do
it and not create huge problems for anyone who uses both cursors and NTs
together ... as those who use either are liable to do.

> What I think we could do, though, is record the Portal's high-level state
> as the number of rows fetched from it. On abort, rewind the Portal and
> then fetch that number of rows again (this is the same method used by
> MOVE ABSOLUTE). We could optimize things a little bit by not doing this
> repositioning until and unless the Portal is actually used again. Still,
> it wouldn't be cheap...

From what you're describing, this seems like the wisest course. I can't
endorse us getting into any situation where *some* operations are rolled back
by an NT abort, and some are not. That seems like begging for 12-hour-long
debugging sessions.

The only cost of doing things transactionally seems to be the performance cost
of re-fetching the Portal in the event of a subtransaction abort containing a
Portal command. If it's a comparison between the performance loss of
re-fetching a Portal, and the debugging nightmare of not knowing what state a
Portal is in after an abort and rollback (consider NTs containing loops),
I'll take the latter any day.

> Of course this only handles SELECT-query portals, not portals that contain
> data-modification commands. But the latter cannot be suspended partway
> through anyhow, so there is no scenario where we need to recover to a
> partly-executed state. (Recall what I said before about not allowing
> continuation of a portal that itself got an error.)

Yes, and the possibility of updatable cursors makes the transactional argument
even more compelling.

--
Josh Berkus
Aglio Database Solutions
San Francisco