Ludovic Pénet wrote:
> Hello,
>
> Reading the TO DO list, I found the following item:
> "Allow cursors to be DECLAREd/OPENed/CLOSEed outside transactions"
>
> I badly need this functionnality to interface postgres in my company
> database abstraction layer. Do you have any idea of when it should be
> available?
> If you think it can be of reasonnable complexity if you give me some hints,
> I can take some time to do it (about one week).
That now depends on your programming skills, how familiar you are with the Postgres code and how you define one
week - or Wieck since it's basically pronounced the same :-) - more like "veek" - but who cares?
Anyway, the basic problem on cursors spanning multiple transactions would be, that currently a cursor in
Postgresis an executor engine on hold. That means, a completely parsed, optimized and prepared execution plan
that'sopened and ready to return result rows on a call to ExecutorRun(). That requires that each of the
scannodes inside the execution plan (the executor nodes that read from a table and return heap tuples according
tothe passed down scankey) has a valid scan snapshot, which in turn requires an existing transaction.
Thus, when opening a cursor that should span multiple transactions, your backend would have to deal
with two transactions, one for what you're doing currently, the other one for what you do with cursors. And here
you're entering the area of big trouble, because Postgres has MVCC, so each transaction has it's own snapshot
viewof the database. So a row you've seen in the Xact of the cursor might have been deleted and reinserted
multipletimes by other transactions until you actually decide to deal with it. Is THAT what you WANT to do? If
so,go ahead, make a proposal and implement the FEATURE. I'd call it a BUG because it follow's the
definition of most M$ features, but that's another discussion.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com