Truly fascinating! Here I am, looking at a quite large query plan and thinking that postgres will partially run this is truly amazing.
By any chance, can you name any text that covers this topic? Book, web site, document, anything would be fine, even non-postgres discussion of the topic would be OK.
No worries if you can't think of a response, you've already helped ;)
Best regards Seref
On Thu, Jul 24, 2014 at 6:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> *cursor* that encapsulates the query, and then read the query result a few
> rows at a time. One reason for doing this is to avoid memory overrun when > the result contains a large number of rows"
> I'm assuming the memory overrun mentioned here is the memory of the client > process connecting to postres.
Right.
> I think when a cursor ref is returned, say > from a function, the query needs to be completed and the results must be > ready for the cursor to move forward.
> If that is the case, there must be a temporary table, presumably with one > or more parameters to adjust its size, (location/tablespace?) etc..
No. The cursor is held as a partially-run execution state tree.
If you declare a cursor WITH HOLD and don't close it before ending the transaction, then we do run the cursor query to completion and store its results in a temporary file (not a full-fledged table). This is to avoid holding the query's resources, such as table locks, across transactions. But in typical cursor use-cases it's not necessary to materialize the full query result at once on either the server or client side.
Regular query execution (without a cursor) doesn't materialize the result on the server side either: rows are spit out to the client as they are computed. libpq is in the habit of accumulating the whole query result before returning it to the client application, but that's just so that its API doesn't need to include the notion of a query failing after having already returned some rows.
