Ruby and PostgreSQL single row mode

Question

I am fetching rows from a PostgreSQL DB using Ruby. This is done in single row mode as described on the pg gem site (https://deveiate.org/code/pg/PG/Result.html)

conn.send_query( "first SQL query" )
conn.set_single_row_mode
conn.get_result.stream_each do |row|
    # do something with the received row of the first query
end

I get all rows of the result set separately as expected. However for large result sets Ruby seems to keep them in memory. This makes the Ruby program terminate as it runs out of memory.

Is there a way to free the space of already processed rows? I think I should use clear() or autoclear? but I am not sure how to use it and what exactly to clear.

Answer 1

Try to use find_each approve (add LIMIT and OFFSET)

limit  = 1000
offset = 0

while do
  conn.send_query("SELECT * FROM users LIMIT #{limit} OFFSET #{offset}")
  conn.set_single_row_mode
  records = conn.get_result

  if records.any?
    limit  += 1000
    offset += limit
    sleep(5)
  else
    break
  end
  records.stream_each do |row|
    # do something with the received row of the first query
  end
end