Re: [GENERAL] Cursors and Transactions, why?
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. 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. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Cursors and Transactions, why?
On Apr 7, 2004, at 7:51 AM, Jan Wieck wrote: 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 [EMAIL PROTECTED]
Re: [GENERAL] Cursors and Transactions, why?
On Apr 7, 2004, at 12:43 AM, Joe Conway wrote: Eric Ridge wrote: On Apr 6, 2004, at 11:54 AM, Jan Wieck wrote: 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. Keep in mind that the tuplestore stays in memory as long as it fits within sort_mem kilobytes. And you can do: More good information. Thanks! Is the tuplestore basically just an array of ItemPointer-s? In mean, it's not a copy of each entire row, is it? eric ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Cursors and Transactions, why?
On 4/6/04 11:09 PM, Tom Lane [EMAIL PROTECTED] wrote: What out of memory thing? The tuplestore code is perfectly capable of spilling to disk --- in fact the usual performance gripe against it has to do with spilling too soon, because sort_mem is set too small. I tried doing a mass update of all rows with a single SQL statement in psql and after it ran for many hours, I got 'out of memory'. I didn't try that using C and WITH HOLD. I assumed it ran out of swap space, but was sleeping at the time. Wes ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Cursors and Transactions, why?
On 4/6/04 10:54 AM, Jan Wieck [EMAIL PROTECTED] wrote: 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. I tried using WITH HOLD in the following case (using an ecpg C program): foreach row in table A update table B with value from table A commit once every 10,000 updates forend I created a cursor on table A. Without WITH HOLD, obviously I got an error on the next TABLE A fetch because the COMMIT closed the cursor. I added 'WITH HOLD' to the cursor. On the first COMMIT, the application hung. I assume the COMMIT would have completed after some period of time, but I didn't wait that long. There are 20 million rows in table A and 60 million in table B (one to many relationship). Is this hang on COMMIT when using WITH HOLD to be expected? Is there a way around it? I don't think it's reasonable put the entire 60 million updates in a single transaction. The kludge solution I implemented was to write out all the data I needed from table A to a file, then read that file and update table B. Wes ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Cursors and Transactions, why?
Eric Ridge wrote: On Apr 6, 2004, at 11:54 AM, Jan Wieck wrote: 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. Keep in mind that the tuplestore stays in memory as long as it fits within sort_mem kilobytes. And you can do: set sort_mem to some_large_number; prior to COMMIT, and then set sort_mem to default; after COMMIT, as long as you can afford the memory use. A bit ugly, but it might come in handy ;-) Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
