Re: libpq - lack of support to set the fetch size

>Albe Laurenz wrote:

I would believe the stackoverflow (
http://stackoverflow.com/questions/21960121/perl-script-fails-when-selecting-data-from-big-postgresql-table)
question referred to explains the issue well.

> You can retrieve the full result set,
not an option because of client memory limitations (in this case it's poor
client spec but there always are some, especially when you want to pull 1e7
rows)

> you can retrieve it row by row,
not an option because of performance (db calls/network roundtrips)

> you can use a LIMIT clause to retrieve it in batches.
you pointed the best why it's not a feasible option (complexity, isolation
levels, not always possible ie. when custom query and last but not least:
far from being elegant)

> CURSOR option
As already explained at stackoverflow - I'm using it as a workaround. My
general point is it forces developers to use lower level communication with
DB (cursors) therefore not as elegant as just setting RowCacheSize
parameter as specified by DBI. According to DBD::Pg maintainer this hasn't
and can't be implemented for PostgreSQL due to the lack of support in its
own libpq library.
So again.., I'm really surprised this functionality is not yet supported in
PostgreSQL. Does that mean everybody have been implementing this through
cursors?

To recap what's on stackoverflow - The functionality I'm talking about
would be an equivalent of JDBC
setFetchSize()<http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setFetchSize(int)>
function
to optimize the load from (any) database in batches, like in the example
below:

Statement st =
conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY);

// Set the fetch size to 1000.

st.setFetchSize(1000);

// Execute the given sql query

String sql = "select * from bigtable";

ResultSet rs = statement.executeQuery(sql);

while (rs.next()) {

⋮

}

where underneath ResultSet.next() doesn't actually fetch one row at a time
from the RESULT-SET. It returns that from the (local) ROW-SET and fetches
ROW-SET (transparently) whenever it becomes exhausted on the local client.

Actually, curious now if this functionality has been implemented in
PostgreSQL JDBC drivers...?

Anyway, according to one of the DBD::Pg developers it's impossible to bring
this functionality as the problem lies deeper, within libpq library:

"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."

So probably the best is to ask Greg to speak to details if still unclear.

Kind Regards,
Maciek

On Mon, Mar 10, 2014 at 9:42 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:

> matshyeq wrote:
> > Postgresql is there for a good while perceived as one of the best (or
> just simply the best!?)
> > available open source DB solution, so I'm really surprised this
> functionality is not yet supported...
>
> You can retrieve the full result set,
> you can retrieve it row by row,
> you can use a LIMIT clause to retrieve it in batches.
>
> Can you explain how exactly the functionality would look that
> you are missing?
>
> Yours,
> Laurenz Albe
>

--
Thank you,
Kind Regards
~Maciek