[Rails] bug in postgresql 'now' time handling??

[Ara.T.Howard]

On Thu, 3 Mar 2005, Dave Steinberg wrote:

>>   - do no synchronization - let the db handle the concept for you.  after 
>> all,
>>     it already knows if it's in a transaction or not.  this means NEVER
>>     calling Time::now to get the code, but doing something like
>> 
>>       def current_timestamp
>>         require 'time'
>>         Time::parse(execute('select current_timestamp').first)
>>       end
>> 
>>       alias now current_timestamp
>> 
>>     instead and ALWAYS using this to obtain the string/timeobj for the 
>> current
>>     time.  i'd have to read more to understand how awful things like 
>> timezone
>>     work there way into this idea though...
>
> I see 3 cases to consider:
>
> 1) Reading the timestamp before the row gets saved.  If you've got a new
> record, and you're asking it for its states, we need to supply some value
> for the timestamp.  In these cases, I would argue that Time.now is a "good
> enough" approximation for PG's now().

unless you are connecting to a remote db.  for instace, in my lab some
machines use utc and some mtn timezone - this would result in QUITE different
results from the two.  of course i think this is dealt with appropriately -
but it's thin ice to be walking...  besides - i'm pretty sure anyone who does
significant dealings with time, especailly intervals, will be shocked that
thing are 'close' when it comes time to generate reports...

> I think some reasonably specialized contortions would be necessary to detect
> the start of a transaction and populate the record with that time.   As its
> written above, if you're working on an unsaved record outside of a
> transaction block, the value you receive by calling 'select
> current_timestamp' would not necessarily match what gets stored in the
> object.  So it may be no better of an approximation than Time.now.

hmmm.  this is true.  both cases are rotten though

   row = Model::new 'timefield' => 'now'  # now mapped to a time string somehow

   # intense computation lasting 15 seconds...

   transaction do
     ...
     # timefield is totally fubar
   end

this bring up a point: it's really an error to substitute anything into this
field.  if the user load a row with 'now' before a transaction it should STAY
that way.  'now' simply must suffice as the string representation.  i know
this could confuse things a bit in rails, but if a user is using postgres he
EXPECTS 'now' to be automagically transformed DURING his transaction and not
before.  if he tries to do date math with that value before, or expects some
strftime'd output for 'now' he's just out of luck and must rftm.  in summary
in don't think 'now' should EVER be substituted by rails when the user has
supplied it - only the db can do this correctly and at the right time.

> 2) Immediately after a record is saved.  Our approximation is now
> out-of-sync with the database here, and we would do well to reload the
> object to match it up.  Perhaps there's some sort of notion of a volatile
> column that we could use here?  Maybe a :reload_after_save symbol we could
> define in the model to trigger this behavior?  This would also be nice if
> you're using a last_modified column with a trigger to keep the timestamp
> fresh and tasty.  I could go on with examples here - modified_by_user, etc.
> Does this already exist?

maybe a column proxy object with to_s overriden?

> 3) When reading an object by id, name, whatever.  In this case everything is
> consistent, since we're reading a literal timestamp out of the database.
> Saving this object will preserve this timestamp, which is desired.

agreed.

> Comments?  Let me see if I can get my rails dev environment back into shape
> and put some code where my mouth is.

ditto.

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