Postgres rails bug testing

readme.md

Hi everyone

I'm trying to hunt down a bug with rails and postgres related to timestamps rounding to the nearest second. To help could you please run this on your rails app. All that's required is a model that has a created_at timestamp and at least one record in the database, you can change the first line of the script to something that fits.

Please comment with your results.

Here's an attempt at a description of the bug: One some machines, when database records are converted to active record objects, the decimal precision is completely lost from timestamps. When we query the database using one of these timestamps, the fractions of a second are included in the SQL, this means that the database query will take into account the incorrect subsecond value of the record, so instead of something like this:

SELECT COUNT(*) FROM "users" WHERE (created_at > '2013-09-16 06:31:33.912670')

You get this:

SELECT COUNT(*) FROM "users" WHERE (created_at > '2013-09-16 06:31:33.000000')

This would not be a problem at all if the subseconds were left off completely like so:

SELECT COUNT(*) FROM "users" WHERE (created_at > '2013-09-16 06:31:33')

But alas they are included.

Here are my results for the test from one machine that shows the bug. My work machine has a very similar setup and does not show the bug. Also some co-workers machines show the bug while others don't.

Bug present?: true
Ruby:         ruby 2.0.0p247 (2013-06-27 revision 41674) [x86_64-darwin12.3.0]
Raw SQL:      2013-03-23 10:01:58.302891
AR Timestamp: 1364032918.0
Rails:        4.0.0
PG Gem:       0.17.0 Revision: 7cdff0a462e5 
Postgres:     psql (PostgreSQL) 9.3.0

On another project, same machine:

Bug present?: true
Ruby:         ruby 2.0.0p247 (2013-06-27 revision 41674) [x86_64-darwin12.3.0]
Raw SQL:      2013-09-24 09:32:33.553674
AR Timestamp: 1380015153.0
Rails:        3.2.13
PG Gem:       0.15.1 Revision: 0bfb6ff650be 
Postgres:     psql (PostgreSQL) 9.3.0

Cheers Mario

postgres_rails_check.rb

model = User # For the test we just need any active record model that has a created_at timestamp.
 
puts %{
==========================
 
Bug present?: #{model.where('created_at > ?', model.last.created_at).any?}
Ruby:         #{RUBY_DESCRIPTION}
Raw SQL:      #{ActiveRecord::Base.connection.execute(model.where('created_at > ?', Time.at(model.last.created_at.to_i)).to_sql).to_a.first['created_at']}
AR Timestamp: #{model.last.created_at.to_f}
Rails:        #{Rails.version}
PG Gem:       #{PG::VERSION} #{PG::REVISION}
Postgres:     #{`psql --version`}
 
==========================
}

When the record is created the timestamp has the fractions, when it gets reloaded from the DB they get lost, eg:

user = User.create(...)
user.created_at.to_f        # => 1380082444.440707
user.reload.created_at.to_f # => 1380082444.0

Also when you query the database directly not using a model it has the fractions:

sql = 'SELECT "users".* FROM "users" ORDER BY "users"."id" DESC LIMIT 1'
Time.parse(ActiveRecord::Base.connection.execute(sql).to_a.first['created_at']).to_f 
#=> 1380082444.440707

==========================
Bug present?: false
Ruby: ruby 2.0.0p247 (2013-06-27 revision 41674) [x86_64-linux]
Raw SQL: 2013-09-24 08:11:44.936302
AR Timestamp: 1380010304.9363022
Rails: 4.0.0
PG Gem: 0.16.0 Revision: 4e0606f5f5aa 
Postgres: psql (PostgreSQL) 9.1.9
contains support for command-line editing

==========================

  User Load (4.3ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" DESC LIMIT 1
   (1.1ms)  SELECT COUNT(*) FROM "users" WHERE (created_at > '2013-09-18 07:36:00.000000')
  User Load (0.6ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" DESC LIMIT 1
   (0.5ms)  SELECT "users".* FROM "users" WHERE (created_at > '2013-09-18 07:36:00.000000')
  User Load (0.5ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" DESC LIMIT 1

==========================

Bug present?: true
Ruby:         ruby 2.0.0p247 (2013-06-27 revision 41674) [x86_64-darwin12.3.0]
Raw SQL:      2013-09-18 07:36:00.15242
AR Timestamp: 1379489760.0
Rails:        4.0.0
PG Gem:       0.17.0 Revision: 7cdff0a462e5
Postgres:     psql (PostgreSQL) 9.3.0


==========================