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@Yahoo.com #
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
