Обсуждение: about cursors

I never used cursors before, and I'm trying to understand how to use
them well.

Postgresql doc says "a cursor that encapsulates the query, and then read
the query result a few rows at a time." So, when I open a cursor, is all
the query executed and results are returned a few a time?

My doubt comes from
http://archives.postgresql.org/pgsql-sql/2005-08/msg00230.php where I
read "when you open a cursor PostgreSQL doesn't know how many
rows it will return". So I start thinking that maybe it does not execute
the whole query....

At this point I'm not able to understand any more if cursor are useful
to reduce computational needs compared to running the same query each
time with limit and offset.

One last question: what happens to unclosed cursors? I mean, suppose an
application opens a cursor and crashes. What happens to that cursor? Is
there a way to close idle cursors?

Thanks.

--
Non c'e' piu' forza nella normalita', c'e' solo monotonia.

Ottavio Campana <ottavio@campana.vi.it> writes:
> Postgresql doc says "a cursor that encapsulates the query, and then read
> the query result a few rows at a time." So, when I open a cursor, is all
> the query executed

No, just enough to give you the rows you ask for.  Otherwise the query
state is held open until the next FETCH.

Exception: if you declare a cursor WITH HOLD then it's executed to
completion before the transaction commits, because the resources
involved in an open query (eg locks) can't be kept across transactions.

Also, depending on how complex the query is, the system might have to do
most of the work before it can deliver even the first row.  ORDER BY
implemented by an explicit sort step is like that, for example.

            regards, tom lane

On Sat, Jun 16, 2007 at 09:58:27AM -0700, Ottavio Campana wrote:
> At this point I'm not able to understand any more if cursor are useful
> to reduce computational needs compared to running the same query each
> time with limit and offset.

A cursor is generally much cheaper because you only execute the query
once. You only have parse/plan/initialise/execute the query once. For
expensive queries this can be a huge saving. If you have a table with
10 million records, a cursor will only go through the table once.

> One last question: what happens to unclosed cursors? I mean, suppose an
> application opens a cursor and crashes. What happens to that cursor? Is
> there a way to close idle cursors?

Cursors are attached to the transactio and session, if either ends, the
cursor dies with it...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout wrote:
>> One last question: what happens to unclosed cursors? I mean, suppose an
>> application opens a cursor and crashes. What happens to that cursor? Is
>> there a way to close idle cursors?
>
> Cursors are attached to the transactio and session, if either ends, the
> cursor dies with it...
>
> Have a nice day,

another question:

since they live in a transaction, how can they be used in web apps?
Suppose you want to display only a subset of records a time in a page,
each time you load a page you have to start a new transaction and
therefore you need a new cursor, or not?

--
Non c'e' piu' forza nella normalita', c'e' solo monotonia.

On lau, 2007-06-16 at 18:58 -0700, Ottavio Campana wrote:
> Martijn van Oosterhout wrote:
> >
> > Cursors are attached to the transactio and session, if either ends, the
> > cursor dies with it...
> >
> > Have a nice day,
>
> another question:
>
> since they live in a transaction, how can they be used in web apps?

as a rule, cursors are not used for web apps.

gnari