Re: Trans-transactions cursors

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