Historical objects should not use `valid_to` to get associations

tagliala commented 5 months ago

so we know. that the validity of an object is an open ended range [t1,t2)

but if there is something associated to this object, the query that chronomodel does by default is '2024-04-12 13:41:12.249642'::timestamp <@ history.cities.validity

tagliala commented 5 months ago

If A1 is valid from [1,4), and we check for relations, we might find a relation with validity [4,6). This would indicate a relation that did not exist during A1's validity.

tagliala commented 5 months ago

Example

country = Country.create name: 'Country'
city = country.cities.create name: 'City'

ApplicationRecord.transaction do
  country.update_column :name, 'Country 2'
  city.update_column :name, 'City 2'
end

Validity ranges

> puts country.history.pluck(:validity).map { |v| "#{v.begin.iso8601(6)}...#{v.end&.iso8601(6)}" }
  Country::History Pluck (1.1ms)  SELECT "history"."countries"."validity" FROM "history"."countries" WHERE "history"."countries"."id" = $1 ORDER BY lower(validity) ASC  [["id", 1]]
2024-04-14T07:33:14.750087Z...2024-04-14T07:35:30.807940Z
2024-04-14T07:35:30.807940Z...

> puts city.history.pluck(:validity).map { |v| "#{v.begin.iso8601(6)}...#{v.end&.iso8601(6)}" }
  City::History Pluck (1.2ms)  SELECT "history"."cities"."validity" FROM "history"."cities" WHERE "history"."cities"."id" = $1 ORDER BY lower(validity) ASC  [["id", 1]]
2024-04-14T07:34:36.158654Z...2024-04-14T07:35:30.807940Z
2024-04-14T07:35:30.807940Z...

Expected Result

country.history.first.cities.first.name
=> "City"

Actual Result

country.history.first.cities.first.name
  Country::History Load (1.3ms)  SELECT "history"."countries".* FROM "history"."countries" WHERE "history"."countries"."id" = $1 ORDER BY lower(validity) ASC LIMIT $2  [["id", 1], ["LIMIT", 1]]
  City Load (0.4ms)  SELECT "cities".* FROM (SELECT "history"."cities".* FROM "history"."cities" WHERE ( '2024-04-14 07:35:30.807940'::timestamp <@ history.cities.validity )) "cities" WHERE "cities"."country_id" = $1 ORDER BY "cities"."id" ASC LIMIT $2  [["country_id", 1], ["LIMIT", 1]]
=> "City 2"

tagliala commented 5 months ago

This also happens for deleted associations.

tagliala commented 4 months ago

select column_name,data_type,datetime_precision from information_schema.columns where table_name = 'countries' and table_schema = 'history' and column_name IN ('validity', 'recorded_at');
 column_name |          data_type          | datetime_precision 
-------------+-----------------------------+--------------------
 recorded_at | timestamp without time zone |                  6
 validity    | tsrange                     |                   
(2 rows)

It seems that datetime precision is not available for the range, and we should assume that the precision is 6. This seems to only be valid in Postgres >= 13

(tsrange uses a timestamp value as subtype)

ifad / chronomodel