Re: libpq - lack of support to set the fetch size - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: libpq - lack of support to set the fetch size |
Date | |
Msg-id | 531DD81D.1060406@aklaver.com Whole thread Raw |
In response to | Re: libpq - lack of support to set the fetch size (matshyeq <matshyeq@gmail.com>) |
List | pgsql-general |
On 03/10/2014 04:51 AM, matshyeq wrote: >>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...? Yes, using a cursor. http://jdbc.postgresql.org/documentation/92/query.html By default the driver collects all the results for the query at once. This can be inconvenient for large data sets so the JDBC driver provides a means of basing a ResultSet on a database cursor and only fetching a small number of rows. ..... > > 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@wien.gv.at > <mailto:laurenz.albe@wien.gv.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 -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: