From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas Lockhart <lockhart@alumni.caltech.edu>
Cc: Kirby Bohling (TRSi) <kbohling@oasis.novia.net>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: cursor interface to libpq
Date: Wed, 20 Sep 2000 02:16:17 -0400
Message-ID: <19106.969430577@sss.pgh.pa.us> (raw)
In-Reply-To: <39C84F0A.9E6C5947@alumni.caltech.edu>
References: <Pine.GSO.4.21.0009191748310.22170-100000@oasis.novia.net>
<39C84F0A.9E6C5947@alumni.caltech.edu>
Thomas Lockhart <lockhart@alumni.caltech.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, lockhart@alumni.caltech.edu, kbohling@oasis.novia.net
Subject: Re: cursor interface to libpq
In-Reply-To: <19106.969430577@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox