>Albe Laurenz wrote:
> you can retrieve it row by row,
> you can use a LIMIT clause to retrieve it in batches.
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.
Kind Regards,
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() 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()) {
⋮
}
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.albe@xxxxxxxxxx> wrote:
matshyeq wrote:You can retrieve the full result set,
> 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 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
