I am grappling with an interesting problem that I can’t believe I’m the first to tackle. Hoping someone has covered this ground before, or is experienced enough to give good suggestions, and can suggest a reasonable path forward.
The issue is related to transactions and ActiveRecord::Timestamp.
Specifically, if we insert or update multiple rows in a single transaction, each row gets a slightly different updated_at/created_at timestamp.
The reason is because instead of using the SQL function NOW() to set the created/updated times, the ruby code generates the timestamps at the time the SQL for the insert/update is created.
The problem is that we need the timestamps on rows added/updated in the same transaction to match (for auditing and perceived atomicity reasons).
As I allude to above, if ActiveRecord used the sql function NOW() to set the created_on/updated_on times, they would all be the time that the database server began the transaction. (See now() explanation at e.g. http://www.postgresql.org/docs/8.2/static/functions-datetime.html )
But I understand that setting updated_on/created_on times to NOW() is tricky through ActiveRecord.
Here are some ideas we’ve thrown around to fix this, and our thoughts on each:
Idea 1: Fetch NOW() from the database at the beginning of the transaction, and use that verbatim for all updated_on/created_on times that need to get set
Thoughts: inconsistent with the Rails model, which always uses the time on the ruby side, not the database. Also adds a roundtrip to the database.
Idea 2: Mixin new modules to ActiveRecord::Base to override the transaction() call, record the time, and then call super(). Also override the Timestamp current_time_from_proper_timezone method to return the time that our transaction() call squirrelled away. Theres a little more to this (clearing the timestamp when the transaction is committed or rolled back), but you get the idea
Thoughts: Modifies ActiveRecord::Base to be more different than might be obvious. We think we prefer the next idea (3).
Idea 3: Subclass ActiveRecord::Base (to say ActiveRecordLocal) and mix in the modules mentioned above into this class.
Thoughts: This makes is clearer that when you’re using ActiveRecordLocal, you’re not getting the stock ActiveRecord::Base behavior. Otherwise this solution is identical to Idea 2.
Idea 4: when then transaction starts, use Timecop::freeze to freeze the time that ruby sees.
Thoughts: We can’t freeze the time for all code in the app while a transaction is pending. Plus Timecop is for testing, not production use-- but most importantly see previous point.
We’ve seen other ideas which we rejected and won’t bring up here.
So, after all this motivation – the questions;
Q1: Has anyone tacked this issue before? How did you solve it? Is there an easy way to accomplish our goal?
Q2: What strategy do you think we should take to fix this issue? (Either one explained above, or another strategy)?
At this point we’re moving forward with Idea 3, but I’d love to hear some experienced people’s input on this topic