Thread: Cursor behavior
HI, I had a question about the cursor internals implementation. When you Fetch next 'n' results without moving the cursors, isthis 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 wouldseem 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 theresults, 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 forany 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
It seems that the task of fetching next n results without moving the cursor seems like too complicated to implement for anyquery that has even a little bit of complication in it... --- On Wed, 12/21/11, Robert Haas <robertmhaas@gmail.com> wrote: > 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 >