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

[Ara.T.Howard]

On Thu, 3 Mar 2005, Dave Steinberg wrote:

> That seems to be slightly outside the scope of the issue though - except with 
> the possibility of Time.now *not* being in UTC if you were expecting it to 
> be.

<snip point conceded>

> Well, how often do you generate reports based on
>
> a) unsaved objects
> b) objects that you *just* saved?  Again, if we can get some kind of 
> :reload_after_save business going on, the question becomes moot.

neither really works.  again, the problem is that not so much that you are
doing reports on the unsaved objects, but that you may have contrainsts set up
in the db that rely on the 'now' field actually being 'now' and not some ruby
string plugged in instead.  i know it's hard to imagine use cases but trust me
there is a whole world of them, read
http://www.codeforpeople.com/lib/ruby/btpgsql/btpgsql-0.2.4/doc/ for some
ideas why.  i'd like to re-implement this in rails.

> I don't agree.  I think if we can have a usable approximation for the case
> where the record is unsaved, then are able to reload the object and make it
> all line up, then we've come to an amicable compromise.  I would not want to
> code my apps to explicitly deal with the string 'now' where they might
> normally encounter a date object.  I think that'd be a step backwards in
> terms of making rails database agnostic.

and how do you approximate this schema in rails?

   [ahoward at localhost ~]$ cat a.sql
   create table prices(
       id int,
       price float,
       valid_starting_at timestamp not null default 'now',
       valid_ending_at timestamp not null default 'infinity'
   );
   insert into prices values (0, 42.0);
   select * from prices where valid_ending_at >= '2005-03-03';

   [ahoward at localhost ~]$ psql -f a.sql
    id | price |     valid_starting_at      | valid_ending_at
   ----+-------+----------------------------+-----------------
     0 |    42 | 2005-03-03 20:07:08.128051 | infinity
   (1 row)

here you'll get the string 'infinity' (valid postgresql for time) mapped back
as the default value instead of a time object.  btw, these strings could come
back as well:

   epoch
   infinity
   -infinity
   now
   today
   tomorrow
   yesterday
   allballs

(yes - that last one is for real)

why chose 'now' as the only one to approximate?  the others come back as
strings instead of a time objects under the current mapping anyhow - yet all
but the infinities can be. (and maybe they should be)

also, even if you do some sort of reload after insert the current setup totally
blows up if you do

   price_beer = Price::new                              # using default of Time::now here!
   price_wine = Price::new 'valid_starting_at' => 'now' # but not here!

   # begin transaction
   # insert price_beer
   # caluclate the distance to the region producing your wine - takes 1 second
   # insert price_wine

now the price of beer and the price of wine will have different valid_starting
times even though both were inserted under a transaction with a time of 'now'!

later, when generating the report of which prices you updated last week -
you'll miss one.

the problem is not the approximation in ruby-land, it's that the value used in
subsequent inserts from ruby-land into the db is not the one specified by the
schema nor one specified by the user/code - it's some random time that's
'pretty close' to those ones.  i agree that in 97% percent of case it'll be
pretty damn close - but we all those kind of bugs are days * number_of_users in
the unmaking.

> Thinking about this just a little bit more, I would argue that this should 
> simply be a FAQ / Wiki entry.  This whole issue is resolved by doing:
>
> class Foo < ActiveRecord::Base
>  protected
>    def after_create ()  reload end
> end

i suppose one answer is, in fact, to say : if you want it right specify your
times as 'now' and do not rely on default mappings...  you still have to code
around the other strings though...  previously i've handled infinite time
using

   class Time
     INF   = INFINITY          =  1.0 / 0
     NINF  = NEGATIVE_INFINITY = -1.0 / 0
     EPOCH = Time.at(0)

     class << INFINITY
       include Comparable
       def <=> o;id == o.id ? 0 : 1;end
     end

     class << NEGATIVE_INFINITY
       include Comparable
       def <=> o;self.id == o.id ? 0 : -1;end
     end

     def coerce(x)
       case x
       when Float
         return [x, to_f]
       when Integer
         return [x, to_i]
       else
         super
       end
     end
   end

so at least i could have an object that compared larger/smaller than all other
times - the to_s method might be nice to override too...

> Creating a new symbol to save a 4 word function seems excessive, but I'm 
> willing to do it if there's a desire.  DHH - comments?

at this point in half-agreement with Dave Lee; i don't think 'now' should come
back as a string - rather it should come back as a string like the rest of of
the special time values.  this is the simplest solution that is totally
correct IMHO.

hopefully i've made my point clear enough at this point and will defer to the
powers that know more.

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