Thread: 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.
Attachment
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.
Attachment
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.
Attachment
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