Re: Cursors and Transactions, why?

On Apr 6, 2004, at 11:54 AM, Jan Wieck wrote:

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

That's a good thing to know.

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

Is vacuum the only thing that would muck with the rows?

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

Okay, so WITH HOLD is actually materializing the entire resultset
(sequential scan or otherwise)? If that's true, you're right, some of
the benefits do go away.

I need to setup a 7.4 test server and play with this some, and figure
out if the benefits are really what I want them to be. I do appreciate
the insight into how cursors work... it helps a lot!

eric