pg GEM method equivalent to setFetchSize() in JDBC

[dar...@gmail.com]

Hi,

I'm wondering whether there exists a method in 'pg' GEM which is equivalent to setFetchSize() in JDBC mentioned at https://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor .

I'm using Amazon Redshift, which is based on PostgreSQL 8.0.2 according to http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html , and want to write a program to access Redshift using pg. Because data size may become big, I thought I should use stream_each_row (http://deveiate.org/code/pg/PG/Result.html#method-i-stream_each_row-doc), but I found that the method was not contained in the pg GEM I had installed onto an Amazon machine. It is because, I guess, the version of PostgreSQL tools/libraries in the environment is so old that Single Row Mode (http://www.postgresql.org/docs/9.2/static/libpq-single-row-mode.html) is not supported. However, I also found that Amazon's documentation (http://docs.aws.amazon.com/redshift/latest/dg/jdbc-fetch-size-parameter.html) mentions "fetch size", so I want to set fetch size parameter using pg GEM.

Could anyone help me?

Takahiko Kawasaki

[Cody Cutrer]

Takahiko,

You're conflating server side cursors with how libpq reads results from the server. The JDBC method is wrapping around the *SQL* methods described at http://www.postgresql.org/docs/9.2/static/plpgsql-cursors.html - i.e. it creates a cursor on the server, then sends a separate SQL query to fetch a batch of them at a time. The stream_each_row PG gem method executes a query as normal, but instead of waiting for the entire result to be passed in memory, reads one row off the connection at a time. Apparently the latter requires some protocol level support from the server, while cursors do not - they were already supported in Postgres 8.0.  HOWEVER - Amazon Redshift is not actually PostgreSQL 8.0 - it's based off of it. So YMMV if even cursors will work. You'll have to dig in to Amazon's documentation (or just try using cursors) to see how they recommend that you pull large amounts of data out of Redshift.

Cody Cutrer

--
You received this message because you are subscribed to the Google Groups "ruby-pg" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ruby-pg+u...@googlegroups.com.
To post to this group, send email to rub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ruby-pg/b496144b-3721-4a40-861e-86eea4e7ccb9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.