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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
