Trans-transactions cursors

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

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

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