When inserting new records, I''d like to use the internal time of the database in the timestamp field (i.e., "NOW()" or "CURRENT_TIMESTAMP") during the save. However, Rails doesn''t seem to like this. I''m cloning an ActiveRecord, modifying some parameters, then saving it. If I do nothing to the timestamp attribute, it gets saved as the cloned value. if I attempt to overwrite it with a string value, like "NOW()", it gets set to NULL, which violates the database rules. Is there a way I can get around this? I''d be happy with deleting the attribute and letting the database use it''s default value (which is NOW()), or I could even run a SQL select in before_save() and get the current database time and update the record prior to saving. Any suggestions?
Caleb Tennis wrote:> When inserting new records, I''d like to use the internal time of the database > in the timestamp field (i.e., "NOW()" or "CURRENT_TIMESTAMP") during the > save. However, Rails doesn''t seem to like this. I''m cloning an > ActiveRecord, modifying some parameters, then saving it.Do you require the database NOW()? If not, you can do before_create { self.foo = Time.now } to set the current time at insert. jeremy
On Wednesday 23 March 2005 01:25 pm, Jeremy Kemper wrote:> Do you require the database NOW()?I''d like to, because there are multiple machines that read and write to this database, and the timestamps are very important for making sure a given client gets the latest and greatest value.
Caleb Tennis <caleb-PfRr3eUzJn1Wk0Htik3J/w@public.gmane.org> writes:> When inserting new records, I''d like to use the internal time of the database > in the timestamp field (i.e., "NOW()" or "CURRENT_TIMESTAMP") during the > save. However, Rails doesn''t seem to like this. I''m cloning an > ActiveRecord, modifying some parameters, then saving it.You can put a datetime field named updated_at in the database. Rails will automatically save the current date and time on saving. You can also create the datetime field created_at where the date of creating the record is stored. Patrice -- the Holy Scriptures, which are able to make you wise for salvation through faith in Christ Jesus. (2 Timothy 3:15)
>> When inserting new records, I''d like to use the internal time of the >> database >> in the timestamp field (i.e., "NOW()" or "CURRENT_TIMESTAMP") during >> the >> save. However, Rails doesn''t seem to like this. I''m cloning an >> ActiveRecord, modifying some parameters, then saving it. > > You can put a datetime field named updated_at in the database. Rails > will automatically save the current date and time on saving. > > You can also create the datetime field created_at where the date of > creating the record is stored.Note that now() and current_timestamp(), as they''re presently written, aren''t 100% right. They''ll give at best a close approximation of the transaction start-time. This has been discussed a bit in the archives (specifically related to PostgreSQL), but I don''t think a patch was ever rolled (I haven''t had time unfortunately). If you''re fine with now() being more similar to wallclock-level precision, then you''re fine. If you need the precise semantics of now(), you''re out of luck for the moment. Regards, -- Dave Steinberg http://www.geekisp.com/ http://www.steinbergcomputing.com/
On Wednesday 23 March 2005 01:54 pm, Dave Steinberg wrote:> If you''re fine with now() being more similar to wallclock-level > precision, then you''re fine. If you need the precise semantics of > now(), you''re out of luck for the moment.The lack of precision is fine for me. What I''m trying to avoid is this: client A : current time 12:00:00 client B : current time 12:01:00 client B inserts a record a few seconds later, client A inserts a record Now, my latest record according to timestamp isn''t correct, because of the offset of clocks between the two clients. If I use the local database time when performing the inserts/updates, this won''t happen.
Caleb Tennis wrote:> The lack of precision is fine for me. What I''m trying to avoid is this: > > client A : current time 12:00:00 > client B : current time 12:01:00 > > client B inserts a record > a few seconds later, client A inserts a record > > Now, my latest record according to timestamp isn''t correct, because of the > offset of clocks between the two clients. If I use the local database time > when performing the inserts/updates, this won''t happen.You can run ntpd on your web nodes to keep their clocks synchronized. jeremy
On Wed, 23 Mar 2005, Jeremy Kemper wrote:> Caleb Tennis wrote: >> The lack of precision is fine for me. What I''m trying to avoid is this: >> >> client A : current time 12:00:00 >> client B : current time 12:01:00 >> >> client B inserts a record >> a few seconds later, client A inserts a record >> >> Now, my latest record according to timestamp isn''t correct, because of the >> offset of clocks between the two clients. If I use the local database time >> when performing the inserts/updates, this won''t happen. > > You can run ntpd on your web nodes to keep their clocks synchronized.this won''t even come close to fixing the problem. search for Subject: bug in postgresql ''now'' time handling?? in the rails archives for why. the rails hanlding of ''now'' is badly broken and can only be fixed with a solution which eventually inserts the string ''now'' into the sql sent to the db. regardless of what the object is or looks in rails this MUST be case or rails time handling will be broken. consider model = MyModelWithTimeStampDefaultingToNow # something which takes 1.5 seconds to run model2 = MyModel2WithTimeStampDefaultingToNow model.save model2.save because rails would have interted the Time object Time::now into both models (due to default handling/schema reflection) each time will be 1.5 seconds apart and this is totally fubar. if a schema looks something like my_models ( fieldname timestamp default ''now'' ); my_model2s ( fieldname timestamp default ''now'' ); and we use rails to update both tables in a transaction rails will hork the times badly for the scenario descirbed above which, of course, should be impossible from the perspective of the schema and user code. there is IS a workaround however: model = MyModelWithTimeStampDefaultingToNow::new ''fieldname'' => ''now'' # something which takes 1.5 seconds to run model2 = MyModel2WithTimeStampDefaultingToNow::new ''fieldname'' => ''now'' model.save model2.save or at least i think this should work... i''m willing to work on this but detected a lack of interest from the rails developers side. not that you guys *have* to be interested - but some guidance/opinion would be nice since i simply do not have intimate enough understanding of the overall design goals of rails to decide on a a fix - though i''m capable of coming up with several ideas. thoughts? kind regards. -a -- ==============================================================================| EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | When you do something, you should burn yourself completely, like a good | bonfire, leaving no trace of yourself. --Shunryu Suzuki ===============================================================================
On Wednesday 23 March 2005 02:33 pm, Ara.T.Howard wrote:> or at least i think this should work... i''m willing to work on this but > detected a lack of interest from the rails developers side. not that you > guys *have* to be interested - but some guidance/opinion would be nice > since i simply do not have intimate enough understanding of the overall > design goals of rails to decide on a a fix - though i''m capable of coming > up with several ideas. thoughts?My current fix is (in my model): def before_save write_attribute("timestamp", connection.execute("SELECT NOW()").to_s ) end However, I''m starting to see a bigger issue here of the handling of default values. My current thought would be to add a configuration hash in ActiveRecord which allows a user to specify columns which should use the DEFAULT value on the database, and simply not update those columns during a save (or, like with postgres, use the value "DEFAULT").
Ara.T.Howard wrote:> On Wed, 23 Mar 2005, Jeremy Kemper wrote: >> You can run ntpd on your web nodes to keep their clocks synchronized. > > this won''t even come close to fixing the problem. search for > Subject: bug in postgresql ''now'' time handling?? > in the rails archives for why. the rails hanlding of ''now'' is badly broken > and can only be fixed with a solution which eventually inserts the string > ''now'' into the sql sent to the db. regardless of what the object is or > looks in rails this MUST be case or rails time handling will be broken. considerThe solution I proposed moves timestamping responsibility from the database to Rails. Use the callback before_create { self.field = Time.now } rather than the SQL trigger field timestamp default CURRENT_TIMESTAMP> model = MyModelWithTimeStampDefaultingToNow > # something which takes 1.5 seconds to run > model2 = MyModel2WithTimeStampDefaultingToNow > model.save > model2.save > > because rails would have interted the Time object Time::now into both models > (due to default handling/schema reflection) each time will be 1.5 seconds > apart and this is totally fubar. if a schema looks something likeActive Record instantiates objects with attribute values set to column defaults. I agree that default handling has some brokenness here, but how is the column default CURRENT_TIMESTAMP translated into two separate Time.now calls 1.5s apart?> my_models (fieldname timestamp default ''now''); > my_model2s (fieldname timestamp default ''now''); > > and we use rails to update both tables in a transaction rails will hork the > times badly for the scenario descirbed above which, of course, should be > impossible from the perspective of the schema and user code.Because now is (by convention) understood to be the time at the beginning of the transaction? One way of solving the problem here is to get Rails out of the way of our uber serious databases. Another way is to give our saucy Ruby a shine and see what she can do about bringing app logic out of our dbs and into our objects: class Time class << self def begin_transaction @transaction_now = now class << self attr_reader :transaction_now alias_method :old_now, :now alias_method :now, :transaction_now end end def end_transaction class << self alias_method :now, :old_now remove_method :transaction_now end end def transaction begin_transaction yield ensure end_transaction end end end Of course we have some thread-safety issues to wring our hands over, but we can mitigate that by making @transaction_now thread-local instead. 4.times { p Time.now; sleep 1 } Time.transaction do 4.times { p Time.now; sleep 1 } end 4.times { p Time.now; sleep 1 } Wed Mar 23 12:33:48 PST 2005 Wed Mar 23 12:33:49 PST 2005 Wed Mar 23 12:33:50 PST 2005 Wed Mar 23 12:33:51 PST 2005 Wed Mar 23 12:33:52 PST 2005 Wed Mar 23 12:33:52 PST 2005 Wed Mar 23 12:33:52 PST 2005 Wed Mar 23 12:33:52 PST 2005 Wed Mar 23 12:33:56 PST 2005 Wed Mar 23 12:33:57 PST 2005 Wed Mar 23 12:33:58 PST 2005 Wed Mar 23 12:33:59 PST 2005> there is IS a workaround however: > > model = MyModelWithTimeStampDefaultingToNow::new ''fieldname'' => ''now'' > # something which takes 1.5 seconds to run > model2 = MyModel2WithTimeStampDefaultingToNow::new ''fieldname'' => ''now'' > > model.save > model2.saveGotcha. This short-circuits the default that Active Record derives from the db schema. When a new record is initialized its attributes are set to the column defaults. This is wrong but pragmatic. To follow the database''s convention, defaults should be set when the record is created (inserted), not when it''s instantiated. But then you couldn''t instantiate a record to show it in a create form with defaults filled in. You''d have to start a transaction, create the record, show the form, wait for the form post, update the record, and commit the transaction. It''s like our app is begging to be pulled out of the database.> or at least i think this should work... i''m willing to work on this but > detected a lack of interest from the rails developers side. not that > you guys > *have* to be interested - but some guidance/opinion would be nice since i > simply do not have intimate enough understanding of the overall design > goals > of rails to decide on a a fix - though i''m capable of coming up with > several > ideas. thoughts?No lack of interest (I followed the earlier thread; not sure whether I''m among "you guys" though ;) I appreciate the constructive approach you''ve taken to your foray into Rails, Ara. I hope Active Record can be a joyful worker bee for you too. Solutions -- all steps desirable: 1. default values are app logic. move them from db to app. 2. fix Active Record default handling to be pragmatic *and* correct 3. introduce scary hacks like transactional Time.now to wow our friends jeremy
> or at least i think this should work... i''m willing to work on this but > detected a lack of interest from the rails developers side. not that you > guys *have* to be interested - but some guidance/opinion would be nice > since i simply do not have intimate enough understanding of the overall > design goals of rails to decide on a a fix - though i''m capable of coming > up with several ideas. thoughts?Thought about this a little more - we could get around this by using symbols. For example: @m = SomeModel.new @m.some_timestamp_attribute = :now @m.save What do you think about this (kind of hackish..will need unit tests): Index: activerecord/lib/active_record/connection_adapters/abstract_adapter.rb ==================================================================--- activerecord/lib/active_record/connection_adapters/abstract_adapter.rb (revision 988) +++ activerecord/lib/active_record/connection_adapters/abstract_adapter.rb (working copy) @@ -176,6 +176,7 @@ def type_cast(value) if value.nil? then return nil end + if value.class == Symbol then return value end case type when :string then value when :text then value @@ -332,6 +333,10 @@ when Float, Fixnum, Bignum then value.to_s when Date then "''#{value.to_s}''" when Time, DateTime then "''#{value.strftime("%Y-%m-%d %H:%M: %S")}''" + when Symbol + if value.to_s == "default_value" + "DEFAULT" + elsif value.to_s == "now" + "NOW()" + else + "NULL" + end
Caleb Tennis wrote:> However, I''m starting to see a bigger issue here of the handling of default > values. My current thought would be to add a configuration hash in > ActiveRecord which allows a user to specify columns which should use the > DEFAULT value on the database, and simply not update those columns during a > save (or, like with postgres, use the value "DEFAULT").Ok. I''ll say it. I think it''s time to put optional column mappings and metadata in our classes. Pull info from database then overlay provided info like SHTYLGCYFLD column names, custom types and classes. Defaults. Validations! And heck, if you want to put all your info there, go ahead and generate your schema from it too. jeremy
Caleb Tennis wrote:> Thought about this a little more - we could get around this by using symbols. > For example: > > @m = SomeModel.new > @m.some_timestamp_attribute = :now > @m.saveThis is cool, but it patches the symptom rather than the cause. Fields with unchanged default values should be inserted as NULL. jeremy
On Wednesday 23 March 2005 04:00 pm, Jeremy Kemper wrote:> Ok. I''ll say it. I think it''s time to put optional column mappings and > metadata in our classes. Pull info from database then overlay provided > info like SHTYLGCYFLD column names, custom types and classes. Defaults. > Validations!More pragmatically, what I''m after here is: record.save record.attribute = "foo" record.save The second save shouldn''t have to resend every column/value again, just those that changed. However, in the smaller picture, I''d be happy if there was some method for me to be able to take care of this locally during a callback, but I don''t seem to have one at the moment.
On Wednesday 23 March 2005 04:07 pm, Jeremy Kemper wrote:> This is cool, but it patches the symptom rather than the cause. Fields > with unchanged default values should be inserted as NULL.That''d be fine with me if postgresql would interpret a NULL value insertion for a NON-NULL column as a default value, but instead it just throws an error.
Caleb Tennis wrote:> On Wednesday 23 March 2005 04:07 pm, Jeremy Kemper wrote: > >>This is cool, but it patches the symptom rather than the cause. Fields >>with unchanged default values should be inserted as NULL. > > > That''d be fine with me if postgresql would interpret a NULL value insertion > for a NON-NULL column as a default value, but instead it just throws an > error.Um. My bad. DEFAULT. Whatever the db wants, the db gets! jeremy
>> That''d be fine with me if postgresql would interpret a NULL value >> insertion >> for a NON-NULL column as a default value, but instead it just throws >> an >> error. > > Um. My bad. DEFAULT. Whatever the db wants, the db gets! > jeremyIndeed - that would be ideal. Then you could have any the result of a function, or a call to a sequence, or whatever other arbitrary thing you wanted as your column default. My only question is what do you present to the ruby world? Do you estimate now() with Time.now? Some of the possible defaults are easily mapped to ruby objects (varchars obviously) - others like now() and function results aren''t. Perhaps a useful approach would be to allow the user to define a set of columns that don''t get saved either on create or update? If I could exclude a column from being saved on create, then I might just be able to sidestep the issue entirely. Somewhat specifically, I''d like to omit the column name / value from the insert statement. Thoughts? Regards, -- Dave Steinberg http://www.geekisp.com/ http://www.steinbergcomputing.com/
On Wed, 23 Mar 2005, Jeremy Kemper wrote:>> this won''t even come close to fixing the problem. search for Subject: bug >> in postgresql ''now'' time handling?? in the rails archives for why. the >> rails hanlding of ''now'' is badly broken and can only be fixed with a >> solution which eventually inserts the string ''now'' into the sql sent to the >> db. regardless of what the object is or looks in rails this MUST be case >> or rails time handling will be broken. consider> The solution I proposed moves timestamping responsibility from the database > to Rails. Use the callback before_create { self.field = Time.now } rather > than the SQL trigger field timestamp default CURRENT_TIMESTAMPi like the idea of this, but think think it''s nearly impossible to implement. for instance, inside of a transaction Time::now should always return the same value. should you could accomplish this you next have to consider the following setup create table items ( sku int, price_dollars int, price_cents int, created_at timestamp default ''now'', primary key (sku) ); create table items_audit_log ( sku int, price_dollars int, price_cents int, created_at timestamp, op text, /* created, updated, deleted */ when timestamp default ''now'', ); next you obviously create triggers so that, whenever a tuple is inserted, updated, or deleted from items the mod type and it''s time is tracked. obviously the time fields ''created_at'' and ''when'' should be identical if run under a transaction. if rails manages time they cannot be. all queries of the audit log would now be rendered useless by rails since the times in items and items_audit_log would be split-brained - argghh. in short, people use dbs like postgresql precisely because they care about there data and it''s integrity and for rails to do something that ''close enough'' in these cases is sneaky and suprising. a solution might be for rails to manange times as a sort of split personality. eg. a time created as Time::now as part of the default column setup should flag itself so. self.created_to_be_now = true in and overridden Time::now. now this will be a nice time object for rails to work with, but the specific db adapters can do something like: sql_string if time.respond_to? ''created_to_be_now'' ''now'' else time.strftime fmt end OR default fields should be filled in by a select on the database, so field = connection.execute ''select current_timestamp'' vs. field = Time::now as is currently done. this would be very correct since, at all times, whether in ruby or postgesql, the object would be totally consistent - but it''d also be expensive and is kind of sneaky... hmmm.> Active Record instantiates objects with attribute values set to column > defaults. I agree that default handling has some brokenness here, but how > is the column default CURRENT_TIMESTAMP translated into two separate > Time.now calls 1.5s apart? > > >> my_models (fieldname timestamp default ''now''); >> my_model2s (fieldname timestamp default ''now''); >> >> and we use rails to update both tables in a transaction rails will hork the >> times badly for the scenario descirbed above which, of course, should be >> impossible from the perspective of the schema and user code. > > Because now is (by convention) understood to be the time at the > beginning of the transaction?exactly! ;-) ''now'' and ''current_timestamp'' will always return the same time under a transaction (for obvious reasons). this is true of postgresl and oracle. dunno about mysql and sqlite though - you''d hope it''d be true...> One way of solving the problem here is to get Rails out of the way of our > uber serious databases. Another way is to give our saucy Ruby a shine and > see what she can do about bringing app logic out of our dbs and into our > objects: > > class Time > class << self > def begin_transaction > @transaction_now = now > class << self > attr_reader :transaction_now > alias_method :old_now, :now > alias_method :now, :transaction_now > end > end > > def end_transaction > class << self > alias_method :now, :old_now > remove_method :transaction_now > end > end > > def transaction > begin_transaction > yield > ensure > end_transaction > end > end > end > > Of course we have some thread-safety issues to wring our hands over, but > we can mitigate that by making @transaction_now thread-local instead.a solution like this could work too - but consider my trigger example. it''s a vast assumption that what rails is doing is the ONLY thing going on in the database under that transaction. indeed - almost any real production db will have quite a few triggers doing various things (like poor mans replication for instnace) and this will hork all that up... i have had exactly the same thought though...>> there is IS a workaround however: >> >> model = MyModelWithTimeStampDefaultingToNow::new ''fieldname'' => ''now'' >> # something which takes 1.5 seconds to run >> model2 = MyModel2WithTimeStampDefaultingToNow::new ''fieldname'' => ''now'' >> >> model.save >> model2.save > > Gotcha. This short-circuits the default that Active Record derives from > the db schema.right - and it''s __ok__. the times in rails are still fubar though because they are slightly off under a transaction when, according to the schema that the user wrote and the transaction that the user started - they should never be... this may not matter - or it might. dunno.> When a new record is initialized its attributes are set to the column > defaults. This is wrong but pragmatic. To follow the database''s > convention, defaults should be set when the record is created (inserted), > not when it''s instantiated. But then you couldn''t instantiate a record to > show it in a create form with defaults filled in. You''d have to start a > transaction, create the record, show the form, wait for the form post, > update the record, and commit the transaction. > > It''s like our app is begging to be pulled out of the database.yes this is very true. and i admit that the rails approach is quite convenient for most cases (slick in fact)... i guess a distinction/generalization of defaults which change and defaults that do not change when they hit disk could address this though?> No lack of interest (I followed the earlier thread; not sure whether I''m > among "you guys" though ;) I appreciate the constructive approach you''ve > taken to your foray into Rails, Ara. I hope Active Record can be a joyful > worker bee for you too.o.k. - glad to hear it. not for selfish reasons - it''s just good to get a clue if i''m totally off base or not (the usual case).> Solutions -- all steps desirable: > 1. default values are app logic. move them from db to app.o.k. - but aren''t they kind of both? i guess there are rails (app) defaults and db defaults as orthogonal concepts... that could be workable.> 2. fix Active Record default handling to be pragmatic *and* correctcorrect is good. ;-)> 3. introduce scary hacks like transactional Time.now to wow our friendsi see your point. unfortunately this smells like major(esque) rails rework - i''m more suited to hacking the Time class and wowing my friends... however, if we are on the subjet it might be wise to think about the situation whereby multi form parms are mapped into objects (Times for example) which then cannot be validated against the ctor args which are, of course, then lost? who has a stick? i see a hornet''s nest! cheers. -a -- ==============================================================================| EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | When you do something, you should burn yourself completely, like a good | bonfire, leaving no trace of yourself. --Shunryu Suzuki ===============================================================================
Caleb Tennis wrote:>My current fix is (in my model): > >def before_save > write_attribute("timestamp", connection.execute("SELECT NOW()").to_s ) >end > > > >OT: What is the purpose of "write_attribute". Doesn''t "self.timestamp = ..." mean the same thing? I had this question after seeing some generated code.
>> >> def before_save >> write_attribute("timestamp", connection.execute("SELECT >> NOW()").to_s ) >> end >>It would, but I had redefined the timestamp= method in this class to handle allowing me to set the timestamp in various ways, and by using write_attribute I was guaranteeing that this worked.