Rails loses precision when deserializing timestamps from Postgresql

Let me try adding that Ruby code again:

class Tmp < ActiveRecord::Base

end
t = Tmp.create(:atime => Time.now)
conn = Tmp.connection
10000.times do 
  conn.select_one "UPDATE tmps SET atime = NOW()"
  ret_time = Tmp.find(t.id).atime
  result = conn.select_one("SELECT atime = #{conn.quote(ret_time)} AS tst, atime FROM tmps WHERE id = #{t.id}")
  
  formatted = ret_time.strftime("%a %b %d %H:%M:%S.#{ret_time.usec} %Z %Y")
  if(result['tst'] != 't')
    raise "oops! Rails value '#{formatted}' not the same as SQL value '#{result['atime']}'"
  else
    STDERR.puts("Rails time '#{formatted}' matches SQL time '#{result['atime']}'")
  end
end

Adding a patch that fixes the issue in 2-3-stable. ActiveRecord::ConnectionAdapters::Column#microseconds did an unnecessary conversion to from Rational to float when calculating the integer number of microseconds. Some terminating decimal numbers in base10 are repeating decimal numbers in base2 (the format of float), and occasionally this causes a rounding error.

I will add a patch for Rails 3 if the problem still exists.

The problem does exist in master (as of f4d174b211effd4cd510c1578bdc391852f4d375), this patch should also apply with minimal fuzzing.

diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
index 520f3c8..2a2e38a 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
@@ -170,7 +170,7 @@ module ActiveRecord
           # '0.123456' -> 123456
           # '1.123456' -> 123456
           def microseconds(time)
-            ((time[:sec_fraction].to_f % 1) * 1_000_000).to_i
+            time[:sec_fraction] ? (time[:sec_fraction] * 1_000_000).to_i : 0
           end
 
           def new_date(year, mon, mday)
-- 
1.6.6.1

Here's an updated patch. time[:sec_fraction] is nil when there are no microseconds and nil.to_f is 0.0. Thus, there needs to be a nil-check before attempting the multiplication.

I'm sort of confused by your test case.

After updating all the records to now(), why would you expect them to match an earlier time?

If you're not inside a transaction, now() will change each time you call it.

Each time through the loop a single row is updated to an arbitrary time. Then that row is retrieved and the time is converted to a Ruby object. The Ruby object is converted back to a string and compared to the original value in the SQL row. Occasionally Ruby will encounter a rounding error and be off by one microsecond. When this happens, the SQL dates are no longer equal and the SQL SELECT statement returns 'f' (false).

The loop is just because it will happen eventually. I could have run this code to find an exact fail case and substituted the NOW() call with the exact timestamp that fails.