Are queries run completely before a Cursor can be used?

From: Seref Arikan <serefarikan(at)gmail(dot)com>
To: PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Are queries run completely before a Cursor can be used?
Date: 2014-07-24 17:33:31
Message-ID: CA+4Thdo7HRrW62F6f69Na7xLxEqqXxLHuCA_-f5ircNiW4aVFw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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"

I'm assuming the memory overrun mentioned here is the memory of the client
process connecting to postres. 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..

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2014-07-24 17:43:42 Re: Are queries run completely before a Cursor can be used?
Previous Message Vasudevan, Ramya 2014-07-24 17:13:04 Re: event triggers in 9.3.4