First, if my understanding of what is happening is in error then
please forgive me.
I am given to understand that when a new AR model object is
initialized then AR obtains from the database, and I suppose caches
for further use, those columns that have defaults assigned and the
default value for each.
Consequently, when the save method is called on said model instance
then all of these columns are included in the generated SQL INSERT
statement and the default values are provided as values. Why? Surely
if any attribute with a default value is not specified in the INSERT
statement then the DBMS will handle the situation. What is the point
of including columns that have not been referenced or set in the
current context in an INSERT statement?
This issue has bitten me because AR does not recognize 'infinity' as a
valid default value for a timestamp column. Consequently AR is
attempting to insert a NULL into a column that has a NOT NULL
constraint. This fails. I cannot code around this because the
datetime class in Rails does not recognize 'infinity' as a valid
value.
ruby-1.8.7-p334 :003 > x.expected_at = 'infinity'
=> "infinity"
ruby-1.8.7-p334 :004 > x.save
ActiveRecord::StatementInvalid: PGError: ERROR: null value in column
"expected_at" violates not-null constraint
To me, it seems both redundant and intrusive to handle column default
values as presently seems the case in AR. If I do not wish to
explicitly set an attribute value in my application why then should
the framework do it for me regardless?
First, if my understanding of what is happening is in error then
please forgive me.
I am given to understand that when a new AR model object is
initialized then AR obtains from the database, and I suppose caches
for further use, those columns that have defaults assigned and the
default value for each.
Consequently, when the save method is called on said model instance
then all of these columns are included in the generated SQL INSERT
statement and the default values are provided as values. Why? Surely
if any attribute with a default value is not specified in the INSERT
statement then the DBMS will handle the situation. What is the point
of including columns that have not been referenced or set in the
current context in an INSERT statement?
This issue has bitten me because AR does not recognize 'infinity' as a
valid default value for a timestamp column. Consequently AR is
attempting to insert a NULL into a column that has a NOT NULL
constraint. This fails. I cannot code around this because the
datetime class in Rails does not recognize 'infinity' as a valid
value.
Why do you need infinity as the default value? Why not make it zero
for example, or even better use null to indicate no date, which would
be more conventional.
Colin
I’m interested in the answer to Byrne’s original question, but I think another solution here is to use ‘9999-12-31’ as the infinite date.
That is quite beside the point. The issue I raise is whether the
framework should be duplicating (poorly) the effect of a built in
function of the the DBMS it is dealing with. The value of the
column default would be immaterial except for the fact that AR reads
it and then explicitly attempts to set unreferenced attributes to
those values, even when it cannot handle the conversion. If AR
simply ignored unchanged attributes on INSERT then this problem
would not exist.
Setting a column value to NULL has a number of side effects in
select queries that I would rather avoid having to deal with in
code. Likewise setting an expected date to one in the past creates
considerable difficulties for validation. The main reason that
infinity and -infinity were added to PostgreSQL (and I believe to
MySQL) was to deal with both these issues and specifically as
default values.
Why do you need infinity as the default value? Why not make it zero
for example, or even better use null to indicate no date, which
would be more conventional.
Colin
That is quite beside the point.
No it isn't, if you look back at my post you will see that I was
replying to your statement:
I cannot code around this because the
datetime class in Rails does not recognize 'infinity' as a valid
value.
I was pointing out how you could code around it.
I agree that it does not answer the query regarding why you are seeing
the problem in the first place.
Colin
I had considered that, or at least something similar, but had
received advice on the PG mailing list to use infinity instead.
Depending upon what I learn I may have to resort to exactly that.
The real question I have is: Why is such much effort devoted to
providing in the framework what is already present in the DBMS? I
cannot see what benefit accrues and there has to be some performance
cost to executing code that serves no real purpose. At the very
least it increases the maintenance burden.
Sincerely,
Your entire reply to my original post was reproduced in my reply and
again herein. In it you only refer to changing the default values
insofar as I can determine from reading it.
Well I certainly consider it desirable to read the defaults from the
database. I'd consider it extremely unintuitive if a validation failed
because some column was not set even though the default value for the
column would have made that validation pass.
Now it would probably be nice if rails spotted that values unchanged
from the defaults were in that state and so didn't try and set them
explicitly (and I vaguely remember that being mentioned a long, long
time ago), but that would require more code, not less.
Fred
As Fred mentions later in the thread your issue here is twofold. One
is that Rails isn't correctly understanding 'infinity', that's
obviously postgresql specific and has no parallel in ruby itself (as
you said). That'll never be fixed.
The next issue you have is that rails is always providing values for
every column when it does an insert, irrespective of whether you've
actually changed those columns. We already support writing only
changed values during update, if you're keen to have this
functionality it should be relatively easy for you to patch that in.
As for why rails does this default behaviour at all, you're missing
the huge benefits of being able to render a form with the correct
default values. You can default a post to a particular category and
when you render the edit form it'll be pre-selected as you'd expect.
Well I certainly consider it desirable to read the defaults from the
database. I'd consider it extremely unintuitive if a validation
failed because some column was not set even though the default
value for the column would have made that validation pass.
Explicitly setting column values when they are not referenced in the
code and have valid defaults on the DBMS is fairly unintuitive as
well. In the validations case, one always has the option to provide
ones own default in the model. In this case one has no way to avoid
the consequences of AR's behaviour. At the very least there should
be some way to limit AR's default setting action to certain columns
or tables; or to exclude specific columns and tables from this
treatment.
Now it would probably be nice if rails spotted that values unchanged
from the defaults were in that state and so didn't try and set them
explicitly (and I vaguely remember that being mentioned a long, long
time ago), but that would require more code, not less.
Rails evidently does spot it: instance#attribute_present?(
:attribute ). It just does not use that information when
constructing the INSERT query.
Which is why, naively, I thought that INSERT behaved the same way. I
will take a look at the code and see what I can do in this respect.
I am looking at this code in activerecord-3.0.6/lib/active_record/
persistence.rb around lines 270-290.
# Initializes the attributes array with keys matching the
# columns from the linked table and the values matching the
# corresponding default value of that column, so that a new
# instance, or one populated from a passed-in Hash, still
# has all the attributes that instances loaded from the
# database would.
def attributes_from_column_definition
self.class.columns.inject({}) do |attributes, column|
unless column.name == self.class.primary_key
attributes[column.name] = column.default
end
attributes
end
end
And I can see two ways to proceed with this for my purposes.
I can simply put a guard condition on the column.default assignments
such that if the default value is nil/null then the attribute is not
set. This quick, fairly innocuous, and handles my problem which is
that the pg adapter does not handle 'infinity' as a default value from
PostgreSQL and thus converts it to nil/null.
Or, I can try to map the way the update method discards unset
attributes onto the create method. That is likely a far bigger job
but is probably more generally valuable to the community. I will
likely take the latter course but I would like to know if any of you
have any comments or know of any considerations I should deal with
before I start.
Actually, it turn out that 'infinity' is supported in Ruby.
Apparently infinity can be represented by assigning the value obtained
by dividing a float by zero.
$ irb
ruby-1.8.7-p334 :001 > infinity = 1.0/0
=> Infinity
ruby-1.8.7-p334 :002 > ninfinity = -1.0/0
=> -Infinity
ruby-1.8.7-p334 :003 >
So, I guess this now qualifies as a bug in the Ruby pg adapter gem.
FWIW - you don't have to divide by zero to get infinity. You can use Float::INFINITY.
jack@shepherd:~$ ruby -v
ruby 1.9.2p180 (2011-02-18 revision 30909) [i686-linux]
jack@shepherd:~$ irb
irb(main):001:0> Float::INFINITY == (1.0/0)
=> true
Jack
This constant isn't available in 1.8.
/play trombone
Thank you Aaron for adding support to AR-3.0.8 for + and - Infinity.
When I get a moment, I still intend to look at changing the INSERT
behaviour to disregard columns whose values have not changed. But
this alteration will deal with the immediate problem that we faced.