Eric Ridge wrote:On Apr 6, 2004, at 11:54 AM, Jan Wieck wrote: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?
Vacuum is the only thing that cares for the dustmites, yes.
And WITH HOLD is strong enough to defend against a vacuum, I hope...
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!
Experience and knowledge can only be replaced by more experience and more knowledge.
Very wise words.
My real problem is that the JDBC drivers (and I assume this is true for all client interfaces) buffer the results of a SELECT in memory, because the backend pushes out all the tuples as the response. I'm not dealing with a large number of rows (only a few thousand), but they've very wide, and many contain fields with multi-megabyte data. In some situations, when I've got a lot of open ResultSets, the JVM throws OutOfMemory errors.
One half-baked thought was to hack the JDBC drivers to have 'em gzip large resultsets in memory. Wouldn't completely solve the problem, but would probably help quite a bit. But the better solution is to use cursors. We're not in a position to upgrade to 7.4 just yet, so we'll just deal with the OutOfMemory errors until we can.
eric
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
