Thread

Trans-transactions cursors

Ludovic Pnet <lpenet@cubicsoft.com> — 2001-09-05T17:31:10Z

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).

Best regards,

Ludovic

Re: Trans-transactions cursors

Chamanya <chamanya@yahoo.com> — 2001-09-10T17:19:05Z

Hi,

I am currently building a small web based app, with postgres as back end. I 
found that in ecpg you can declare and use cursor without declaring a 
transaction. In several places I have used cursors for selects only. That's 
the only way I found to make ecpg fetch multiple rows.

And in ecpg I have to give an explicit open cursor statement to make fetching 
possible.

I am usig 7.1.2.

 HTH

 Shridhar 

On Wednesday 05 September 2001 23:01, Ludovic Pénet wrote:
> "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?

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Re: Trans-transactions cursors

Jan Wieck <janwieck@yahoo.com> — 2001-09-11T06:50:19Z

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 Postgres is
    an executor engine on hold. That means, a completely  parsed,
    optimized and prepared execution plan that's opened and ready
    to return result rows  on  a  call  to  ExecutorRun().   That
    requires  that  each  of  the scan nodes inside the execution
    plan (the executor nodes that read from a  table  and  return
    heap tuples according to the 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 view of the database. So  a
    row  you've  seen  in  the Xact of the cursor might have been
    deleted and reinserted multiple times 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

Re: Trans-transactions cursors

Christof Petig <christof@petig-baender.de> — 2001-09-18T12:05:03Z

Chamanya wrote:

> I am currently building a small web based app, with postgres as back end. I
> found that in ecpg you can declare and use cursor without declaring a
> transaction. In several places I have used cursors for selects only. That's
> the only way I found to make ecpg fetch multiple rows.
>
> And in ecpg I have to give an explicit open cursor statement to make fetching
> possible.

That's simply because ecpg starts a new transaction on any SQL statement if no
transaction is active.
I consider this (autocommit on) one of the worst traps you can lay for yourself.

Christof