Обсуждение: Are queries run completely before a Cursor can be used?
Greetings,
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"
Is this how cursors work internally? I can't imagine the complexity of managing cursor operations in anything other than extremely simple sql queries.
Any comments and/or pointers to documentation which explains this would be much appreciated.
Best regards
Seref
Best regards
Seref
Seref Arikan <serefarikan@gmail.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
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. Best regards
Seref
On Thu, Jul 24, 2014 at 6:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Seref Arikan <serefarikan@gmail.com> writes:> *cursor* that encapsulates the query, and then read the query result a few
> 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> rows at a time. One reason for doing this is to avoid memory overrun whenRight.
> 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.No. The cursor is held as a partially-run execution state tree.
> 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..
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