Re: libpq - lack of support to set the fetch size - Mailing list pgsql-general

Daniel Verite wrote:
> matshyeq wrote:

[ runs out of memory on the client because all results from a large query are retrieved at once ]

>> "Unfortunately, this is a limitation in the underlying driver (libpq) rather
>> than DBD::Pg itself. There have been talks over the years of supporting
>> this, but nothing concrete yet. Your best bet would be to ask about this on
>> the Postgres lists"
>>
>> Would you consider putting this on the roadmap, so one day it gets improved?
>
> This improvement seems to have actually been made since 9.2 with
> the PQsetSingleRowMode() function:
>
> http://postgresql.org/docs/current/static/libpq-single-row-mode.html

Yes, DBD::Pg could be improved to make use of that; the problem is probably
that the code would have to differentiate between PostgreSQL versions.

Your solution with using
   SELECT ... OFFSET ? LIMIT 1
in a loop is bound to suck.

First of all, there is no guarantee that the rows will be returned in
the same order each time, see for example
http://www.postgresql.org/docs/current/static/runtime-config-compatible.html#GUC-SYNCHRONIZE-SEQSCANS
Also, unless you operate with an isolation level higher than READ COMMITTED,
the various SELECTs could operate on different data sets.

So you are likely to end up with incorrect results sooner or later
if you use OFFSET and LIMIT without an ORDER BY clause.

Then you will have really bad performance, especially with a large table,
because each SELECT statement will have to start scanning the table again.
The complexity will rise from O(n) to O(n^2).

You can improve on this by using ORDER BY with an index and remembering
the last returned row (get and read http://sql-performance-explained.com/).

Finally, you will have a client-server round trip for each row returned.
This is a problem you would also have when using PQsetSingleRowMode().

Yours,
Laurenz Albe