Re: Are queries run completely before a Cursor can be used?

Thanks Tom,
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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Seref Arikan <serefarikan(at)gmail(dot)com> writes:
> > The documentation for Cursors at
> > http://www.postgresql.org/docs/9.2/static/plpgsql-cursors.html says
> that:
> > "Rather than executing a whole query at once, it is possible to set up a
> > *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.
>
> regards, tom lane
>