Rails created_at timestamp order disagrees with id order

Question

I have a Rails 2.3.5 app with a table containing id and created_at columns. The table records state changes to entities over time, so I occasionally use it to look up the state of an entity at a particular time, by looking for state changes that occurred before the time, and picking the latest one according to the created_at timestamp. For 10 out of 1445 entities, the timestamps of the state changes are in a different order to the ids, and the state of the last state change differs from the state which is stored with the entity itself, e.g.

  id  |     created_at      | entity_id | state |
------+---------------------+-----------+-------+
 1151 | 2009-01-26 10:27:02 | 219       | 1     | 
 1152 | 2009-01-26 10:27:11 | 219       | 2     | 
 1153 | 2009-01-26 10:27:17 | 219       | 4     | 
 1154 | 2009-01-26 10:26:41 | 219       | 5     | 

I can probably get around this by ordering on id instead of timestamp, but can't think of an explanation as to how it could have happened. The app uses several mongrel instances, but they're all on the same machine (Debian Lenny); am I missing something obvious? DB is Postgres.

Answer 1

Because Rails is using database sequence to fetch the new id for your id field (at least in PostgreSQL) on insert or with the RETURNING keyword if the database supports it.

But it updates the created_at and updated_at fields on create with ActiveRecord::Timestamp#create_with_timestamps method which uses the system time.

The row 1154 was inserted later, but the timestamp for created_at field was calculated before.