mariovisic/postgres_rails_check.rb

## readme.md

      
    Raw
  

              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`}

==========================
}
	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`}

	==========================
	}