Re: Cursors and Transactions, why?

Eric Ridge wrote:

> Why must a cursor be defined in an open transaction? Obviously there's
> a good reason, but I can't figure it out. On a high level, what would
> be involved in allowing a cursor to outlive the transaction that
> created it?

Because the transaction is what protects the rows that build the result
set from being removed by vacuum. In PostgreSQL, a cursor is a running
query executor just sitting in the middle of its operation. If the
underlying query is for example a simple sequential scan, then the
result set is not materialized but every future fetch operation will
read directly from the base table. This would obviously get screwed up
if vacuum would think nobody needs those rows any more.

>
> Cursors seem as if they have some nice performance benefits (esp. if
> you're not using all rows found), but their usefulness drops
> considerably since you must leave a transaction open.

And now you know why they are so good if you don't use all rows. This
benefit I think goes away if you use Joe Conway's suggestion of WITH HOLD.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #