Re: [GENERAL] How to handle bogus nulls from ActiveRecord
On Thu, May 12, 2011 20:30, Eric Hu wrote: David suggested using a guesstimate default date along with a boolean to indicate when you're using guesstimates. I think this is a solid approach, but if the default expected_by idea doesn't work for you, a boolean would still make this a lot easier on the Rails side. Since this part of the project is still in development, albeit partially in use for some purposes, all those approaches are under cosnideration considered. In fact some things are implemented in that fashion for other parts of the system, particularly with respect to current status. However, the main problem to be solved is this issue over the default values for time stamp columns, all of which must have a NOT NULL constraint since we simply cannot allow an errant application overwrite valid data, as would have happened in the case under consideration. I am coming to the conclusion that an arbitrary value of 1231 is a better approach than using PGs built-in idea of 'infinity' since I was told that concept is not supported in Ruby. 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. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to handle bogus nulls from ActiveRecord
2011/5/13 James B. Byrne byrn...@harte-lyne.ca:
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.
humm.. interesting... i did some tests here...
[testdb]
SELECT * from infinity_date_test;
id | created_at
+
1 | infinity
2 | infinity
3 | infinity
4 | infinity
5 | 2011-05-13
--- test.rb ---
require 'active_record'
ActiveRecord::Base.establish_connection({
:adapter = 'postgresql',
:database = 'testdb',
:port = 5434,
:host = 'localhost',
:username = 'guedes',
:password = 'guedes'
})
class InfinityDateTest ActiveRecord::Base
set_table_name 'infinity_date_test'
end
InfinityDateTest.all.each do |row|
puts #{row.id} | #{row.created_at} | #{row.created_at.class}
end
i = InfinityDateTest.new
i.created_at = Date::Infinity.new
i.save
and the output
1 | | NilClass
2 | | NilClass
3 | | NilClass
4 | | NilClass
5 | 2011-05-13 | Date
/home/dba/.rvm/gems/ruby-1.9.2-p0@rails3/gems/activerecord-3.0.5/lib/active_record/connection_adapters/abstract_adapter.rb:207:in
`rescue in log': PGError: ERRO: sintaxe de entrada é inválida para
tipo date: --- !ruby/object:Date::Infinity
(ActiveRecord::StatementInvalid)
d: 1
LINE 1: ... INTO infinity_date_test (created_at) VALUES ('--- !ruby...
^
: INSERT INTO infinity_date_test (created_at) VALUES ('---
!ruby/object:Date::Infinity
d: 1
') RETURNING id
...
Well, fetching from database it came nil and when saved into, it was
trying to save a serialized object. From postgresql_adapter.rb [1] you
can see that it returns the correct internal type based when field
type is datetime, but i can't see the same thing for 'date', i suppose
that it going [2] to 'super' [3], so I suppose that this method [4]
should be override in 'postgresql_adapter.rb'.
[1]
https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L85-86
[2]
https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L117-118
[3]
https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/column.rb#L253-254
[4]
https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/column.rb#L197-219
Best regards,
--
Dickson S. Guedes
mail/xmpp: gue...@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to handle bogus nulls from ActiveRecord
On Fri, May 13, 2011 11:50, Dickson S. Guedes wrote: Well, fetching from database it came nil and when saved into, it was trying to save a serialized object. From postgresql_adapter.rb [1] you can see that it returns the correct internal type based when field type is datetime, but i can't see the same thing for 'date', i suppose that it going [2] to 'super' [3], so I suppose that this method [4] should be override in 'postgresql_adapter.rb'. I have opened an issue for this with the ActiveRecord folks. https://github.com/rails/rails/issues/544 -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to handle bogus nulls from ActiveRecord
It is required for application data verification filters that default values for table columns are known to ActiveRecord when creating a new row. So ActiveRecord obtains the default values from the tables dynamically and assigns them to their appropriate column attributes. The problem we encounter arises because ActiveRecord then uses those column assignments when inserting a row even if the column is not otherwise referenced. I am developing a web application using the Ruby on Rails framework with PostgreSQL as the back-end store. In one of our tables we have a column called expected_by which is a time-stamp. It is set to NOT NULL DEFAULT 'INFINITY'. However, Ruby has no concept of infinity and whatever the PostgreSQL adapter is returning for it ActiveRecord receives as nil which is converted to NULL. So, the real fix to this is to alter the persistence class so that columns with default values are not explicitly set to those values on insert. This is unlikely to happen in the short term and will take some time to be integrated into the framework even when it is completed, if ever, So solve this for the moment what I think I require is a trigger on expected_at which tests for NULL on insert and converts it to infinity. The other alternative is to simply set the default to some valid, but unreachable, date like -12-31. I would like other opinions about how to best handle this situation and observations on what other significant concerns I may not be aware of but should provide for. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to handle bogus nulls from ActiveRecord
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of James B. Byrne Sent: Thursday, May 12, 2011 9:12 AM To: pgsql-general@postgresql.org Subject: [GENERAL] How to handle bogus nulls from ActiveRecord So solve this for the moment what I think I require is a trigger on expected_at which tests for NULL on insert and converts it to infinity. The other alternative is to simply set the default to some valid, but unreachable, date like -12-31. Not a huge fan of Infinity as a value...but that just may be lack of experience. I'd probably remove the NOT NULL constraint on expected_at and deal with tri-value logic; or also include a boolean (is_expected) and form queries like NOT is_expected OR (is_expected AND expected_at op timestamp) is_expected could be a calculated value in a view to make things somewhat easier; otherwise you'd need a table constraint to ensure non-null expected has a true is_expected. Without more info as to how you use expected_at other advice is difficult but can you user a meaningful value (say now()+'30 days'::interval) for the default? David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to handle bogus nulls from ActiveRecord
On Thu, May 12, 2011 12:40, David Johnston wrote: Not a huge fan of Infinity as a value...but that just may be lack of experience. I'd probably remove the NOT NULL constraint on expected_at and deal with tri-value logic; or also include a boolean (is_expected) and form queries like Well, actually, the reason for the NOT NULL constraint is to catch application errors exactly like this one. Removing it is not contemplated. I had no idea that AR actually 'copied' and used default values on columns that were not referenced in the application code until I encountered this. And had it gone undetected this would have been a major problem later on. As it was, our tests brought it to our attention quite early which is why we can contemplate several solutions. Without more info as to how you use expected_at other advice is difficult but can you user a meaningful value (say now()+'30 days'::interval) for the default? The column expected_by contains an estimated time of arrival for a particular conveyance. When a row is initialized this value is unknown some of the time. The expected_by value is reset to the arrived_at value on UPDATE if and only if expected_by is greater than arrived_at. Conveyances that have +infinite expected_by time-stamps are considered pending. At some point conveyance rows that are never going to arrive are otherwise flagged. On the other hand, rows with overdue expected_by values are given somewhat more attention, to put it mildly. So, we either fix the problem with AR, possibly by moving to Sequel ORM for this case, although I have not yet received an answer as to whether it does any better; Or we trap and override NULL values with infinity in a trigger; Or we choose for the default value a fixed date far, far into the future. +Infinity was chosen as a default to avoid the complexities of dealing with NULL logic in SELECTS. I suppose that the simplest solution is to go with a date of -12-31 and treat that value like infinity. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to handle bogus nulls from ActiveRecord
The column expected_by contains an estimated time of arrival for a particular conveyance. When a row is initialized this value is unknown some of the time. The expected_by value is reset to the arrived_at value on UPDATE if and only if expected_by is greater than arrived_at. Conveyances that have +infinite expected_by time-stamps are considered pending. At some point conveyance rows that are never going to arrive are otherwise flagged. On the other hand, rows with overdue expected_by values are given somewhat more attention, to put it mildly. So, we either fix the problem with AR, possibly by moving to Sequel ORM for this case, although I have not yet received an answer as to whether it does any better; Or we trap and override NULL values with infinity in a trigger; Or we choose for the default value a fixed date far, far into the future. +Infinity was chosen as a default to avoid the complexities of dealing with NULL logic in SELECTS. I suppose that the simplest solution is to go with a date of -12-31 and treat that value like infinity. The just make it work solution has many merits - I would also probably just use -12-31 as a close approximation for +infinity; which itself is just there because you are avoiding estimate is unknown. Why bother updating the expected_by value once the conveyance is no longer pending? Do you not really care if something arrived early? Even if you do not currently it seems a waste to throw out the data when you can readily get the same result as-needed (CASE WHEN expected_by = arrived_at THEN arrived_at ELSE expected_by END) without giving up the ability to calculate early-ness. It would make more sense to set expected = arrived if and only if expected = 'Infinity'. Still, it would at least seem reasonable to guess a reasonable expected date if one is not otherwise provided - possibly with a flag indicating that it is a true guestimate instead of a estimate. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to handle bogus nulls from ActiveRecord
On Thu, May 12, 2011 15:51, David Johnston wrote: +Infinity was chosen as a default to avoid the complexities of dealing with NULL logic in SELECTS. I suppose that the simplest solution is to go with a date of -12-31 and treat that value like infinity. The just make it work solution has many merits - I would also probably just use -12-31 as a close approximation for +infinity; which itself is just there because you are avoiding estimate is unknown. Why bother updating the expected_by value once the conveyance is no longer pending? Do you not really care if something arrived early? Even if you do not currently it seems a waste to throw out the data when you can readily get the same result as-needed (CASE WHEN expected_by = arrived_at THEN arrived_at ELSE expected_by END) without giving up the ability to calculate The main reason to update expected_by is that sometimes the conveyance arrives without the expected_by ever being set. Leaving the expected_by value at infinity, or 1231, or NULL, complicates other parts of the system. However, leaving untouched expected_by values that are less than the infinite value is doable and is a better approach. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to handle bogus nulls from ActiveRecord
David suggested using a guesstimate default date along with a boolean to indicate when you're using guesstimates. I think this is a solid approach, but if the default expected_by idea doesn't work for you, a boolean would still make this a lot easier on the Rails side. It sounds like you're using a setup for Heroku, so I checked the postgreSQL 8.3 manual--a boolean is 1 bytehttp://www.postgresql.org/docs/8.3/interactive/datatype-boolean.html. If this isn't for Heroku, other postgreSQL version probably implement booleans the same way. Your database size should go up by # records * 1 byte + indexing overhead. Though I don't know how many records you're working with, this seems relatively cheap given that it will make your code more readable (if expected_date_estimated?). It should also simplify any remaining code you have to write, as you won't have to think about writing elaborate if or case statements to determine if expected_by was explicitly set. On Thu, May 12, 2011 at 1:06 PM, James B. Byrne byrn...@harte-lyne.cawrote: On Thu, May 12, 2011 15:51, David Johnston wrote: +Infinity was chosen as a default to avoid the complexities of dealing with NULL logic in SELECTS. I suppose that the simplest solution is to go with a date of -12-31 and treat that value like infinity. The just make it work solution has many merits - I would also probably just use -12-31 as a close approximation for +infinity; which itself is just there because you are avoiding estimate is unknown. Why bother updating the expected_by value once the conveyance is no longer pending? Do you not really care if something arrived early? Even if you do not currently it seems a waste to throw out the data when you can readily get the same result as-needed (CASE WHEN expected_by = arrived_at THEN arrived_at ELSE expected_by END) without giving up the ability to calculate The main reason to update expected_by is that sometimes the conveyance arrives without the expected_by ever being set. Leaving the expected_by value at infinity, or 1231, or NULL, complicates other parts of the system. However, leaving untouched expected_by values that are less than the infinite value is doable and is a better approach. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
