From: Douglas Thomson <dougt@mugc.cc.monash.edu.au>
To: tgl@sss.pgh.pa.us
Cc: dougt@mugc.cc.monash.edu.au
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Front end memory consumption in SELECT
Date: Sun, 21 Nov 1999 17:14:42 +1100 (EST)
Message-ID: <199911210614.RAA02176@mugca.cc.monash.edu.au> (raw)
In-Reply-To: <6505.943079195@sss.pgh.pa.us>
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Douglas Thomson <dougt@mugc.cc.monash.edu.au> writes:
> > However, judging by the memory consumption of my front-end process,
> > it would seem that the SELECT is loading the entire table into memory
> > before I even fetch the first row! Can anyone confirm that this is in
> > fact what goes on?
>
> libpq handles SELECTs that way. You should consider DECLARE CURSOR
> and FETCH if you need to retrieve a large query result in chunks.
Thanks for this suggestion, it has solved my problem very nicely.
In case any Perl interface users are reading this and interested,
this meant changing my old code that used to work like:
my $result = $conn->exec("SELECT * from big_table");
$result->resultStatus == PGRES_TUPLES_OK or die(...);
while (my @tuple = $result->fetchrow) {
...
}
to instead work like:
my $result = $conn->exec(
"DECLARE big_cursor CURSOR FOR SELECT * FROM big_table");
$result->resultStatus == PGRES_COMMAND_OK or die(...);
do {
$result = $conn->exec("FETCH 100 FROM big_cursor");
$result->resultStatus == PGRES_TUPLES_OK or die(...);
while (my @tuple = $result->fetchrow) {
...
}
} while ($result->ntuples == 100);
$conn->exec("CLOSE big_cursor"); # if not about to COMMIT anyway
which limits to 100 the number of tuples in memory at any one time.
I only found two cases that I could not handle this way: SELECT FOR
UPDATE (since PostgreSQL cursors are read-only) and selects done
outside of transactions. In my application both these exceptions only
affected quite small selections (usually just one tuple) so I was
able to revert to the non-cursor select with no problem.
For what it is worth, I measured no significant difference in the
total time taken to traverse the entire table, but the reduction in
memory consumption was dramatic. Note that fetching only a single
tuple at a time (instead of the 100 shown above) did take much
longer.
Hope this information helps someone,
Doug.
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: dougt@mugc.cc.monash.edu.au, tgl@sss.pgh.pa.us
Subject: Re: [INTERFACES] Front end memory consumption in SELECT
In-Reply-To: <199911210614.RAA02176@mugca.cc.monash.edu.au>
* 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