Skip site navigation (1) Skip section navigation (2)

Re: cursor interface to libpq

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
Cc: "Kirby Bohling (TRSi)" <kbohling(at)oasis(dot)novia(dot)net>, pgsql-interfaces(at)postgresql(dot)org
Subject: Re: cursor interface to libpq
Date: 2000-09-20 06:16:17
Message-ID: 19106.969430577@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-interfaces
Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
>> I am trying to run a select statement, and I keep running out of
>> memory.  I have noticed that libpq has the nice feature that I don't have
>> to request each row one at a time.  After a little investigate, I found
>> that libpq appears to get the entire result set at once.  Well, at least
>> PQgetvalue() doesn't appear to do anything besides return a pointer to a
>> string.  There is no lazy evaluation.  It doesn't just fetch the row I
>> need, and flush old ones as memory permits.

> Use a cursor at the query level to keep the result set in the backend.
> But you will still (possibly) run out of memory, since the *backend*
> must keep the result set in memory and/or on disk.

Er, no.  DECLARE CURSOR does not cause the backend to buffer the whole
result set; it just stores the state of the executor's plan tree.  AFAIK
you can deal with an indefinitely large result set if you use a cursor
and fetch just a limited number of rows at a time.

The bottleneck here is mainly that libpq's API is defined in terms of
providing random access to a result set, no matter how large --- so
libpq has to buffer the whole result set in client memory.

Aside from random access there are also error-reporting issues.
Currently libpq guarantees to tell you about any errors encountered
during a query before you start to read result rows.  That guarantee
wouldn't hold in a streaming-results scenario.

These issues have been discussed quite a few times before --- see the
pg-interfaces archives.  I think everyone agrees that it'd be a good
idea to have a streamable libpq interface, but no one's stepped up to
the plate to define or implement one...

			regards, tom lane

In response to

pgsql-interfaces by date

Next:From: DAANEN VincentDate: 2000-09-20 07:44:59
Subject: pgadmin
Previous:From: Thomas LockhartDate: 2000-09-20 05:45:46
Subject: Re: cursor interface to libpq

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group