Should PG::Result#clear be called after you've executed raw SQL?

Question

When you use ActiveRecord::Base.connection.execute(sql_string), should you call clear on the result in order to free memory?

At 19:09 in this podcast, the speaker (a Rails committer who has done a lot of work on Active Record) says that if we use ActiveRecord::Base.connection.execute, we should call clear on the result, or we should use the method ActiveRecord::Base.connection.execute_and_clear, which takes a block.

(He’s a bit unclear on the method names. The method for the MySQL adapter is free and the method for the Postgres adapter is clear. He also mentions release, but that method doesn't exist.)

My understanding is that he's saying we should change

result = ActiveRecord::Base.connection.execute(sql_string).to_a
process_result(result)

to

ActiveRecord::Base.connection.execute_and_clear(sql_string, "SCHEMA", []) do |result|
  process_result(result)
end

or

result = ActiveRecord::Base.connection.execute(sql_string)
process_result(result)
result.clear

That podcast was the only place I've heard this claim, and I couldn't find any other information about it. The Rails app I'm working on uses execute without clear in a number of instances, and we don't know of any problems caused by it. Are there certain circumstances under which failing to call clear is more likely to cause memory problems?

Answer 1

It depends on the adapter. Keep in mind that Rails doesn't control the object that is returned by execute. If you're using PostgreSQL, you'll get back a PG::Result, and using the mysql2 adapter, you'll get back a Mysql2::Result.

For PG (documented here), you need to call clear unless autoclear? returns true or you'll get a memory leak. You may also want to call clear manually if you've got a large enough result set to ensure it doesn't cause memory issues before it gets cleaned up.

Mysql2 doesn't appear to expose its free through the Ruby API, and appears to always clean itself up during GC.