Cursor behavior
HI,
I had a question about the cursor internals implementation. When you Fetch next 'n' results without moving the cursors, is this kind of functionality implemented by firstly executing the whole query and then moving the cursor over the results, or are the movements done on active database lookups, moving forward and backward...
it seems that the cursor implementation functions evaluate the whole query and then return results as requested, it would seem to be too difficult to otherwise support a functionality that returns next 10 results without moving the cursors... we need to determine this since in our case we are
dealing with a query that returns millions of records, and we would rather the cursor did not evaluate the whole query upfront...
any help is greatly appreciated...
thanks
On Thu, Dec 15, 2011 at 4:15 PM, amit sehas <cun23@yahoo.com> wrote:
I had a question about the cursor internals implementation. When you Fetch next 'n' results without moving the cursors, is this kind of functionality implemented by firstly executing the whole query and then moving the cursor over the results, or are the movements done on active database lookups, moving forward and backward...
I think it depends on the query. For example, I believe that a query
involving writeable CTEs will be run to completion before returning
any results, but I believe that a seqscan will not.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
It seems that the task of fetching next n results without moving the cursor seems like too complicated to implement for any query that has
even a little bit of complication in it...
--- On Wed, 12/21/11, Robert Haas <robertmhaas@gmail.com> wrote:Show quoted text
From: Robert Haas <robertmhaas@gmail.com>
Subject: Re: [HACKERS] Cursor behavior
To: "amit sehas" <cun23@yahoo.com>
Cc: pgsql-hackers@postgresql.org
Date: Wednesday, December 21, 2011, 8:43 AM
On Thu, Dec 15, 2011 at 4:15 PM, amit
sehas <cun23@yahoo.com>
wrote:I had a question about the cursor internals
implementation. When you Fetch next 'n' results without
moving the cursors, is this kind of functionality
implemented by firstly executing the whole query and then
moving the cursor over the results, or are the movements
done on active database lookups, moving forward and
backward...I think it depends on the query. For example, I
believe that a query
involving writeable CTEs will be run to completion before
returning
any results, but I believe that a seqscan will not.--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Wed, Dec 21, 2011 at 11:44 AM, amit sehas <cun23@yahoo.com> wrote:
It seems that the task of fetching next n results without moving the cursor seems like too complicated to implement for any query that has
even a little bit of complication in it...
I think that's probably true. It would also be expensive if you did
implement it; who wants to go re-execute a join to back up the scan?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company