5 ms·

Another commenter mentioned it already, but it’s worth repeating. You can solve this problem by introducing temporal concepts. We rely strongly on this at my p

by gen220 7y ago

Another commenter mentioned it already, but it’s worth repeating. You can solve this problem by introducing temporal concepts. We rely strongly on this at my place of work, it really works! Essentially, you just need two tstzrange columns, representing: (1) when the row was a “valid” representation of the key, (2) when the row could have been used to conduct other “transactions”. With a valid period and a transaction period, you have a history of the values of an object, and the ability to make non-destructive updates to that object. It’s an essential component of any audit-worthy system, because it empowers you to trivially answer the question “what did our database think John’s 2018/01/01 address was on the day that we mailed him a check on 2017/12/15”?

andrei_says_ 7y ago
I imagine you also save changes to the address as separate versions so you can query them in the future?
- gen220 7y ago
  Yes. In practice, the typical "UPDATE" looks like this: 1. create a "hole" in the current transaction space's valid space (through expirations of records in transaction time, valid time). 2. insert the new version at (transaction_period=(now, inf), valid_period=(effective_date, inf)). It sounds complicated, and it sometimes is, but in practice most of the hard work (specifically around transaction periods) is performed for you by triggers. Usually you only have to think about valid periods, as a developer, which is easier to wrap your mind around. The unfortunate thing is that there aren't a lot of very straightforward open source implementations of bitemporal triggers. The ones that exist are mostly designed to be theoretically sound and feature-complete, rather than usable. At my company, we have our own implementation that has slowly grown over the years. It's about 500 lines of SQL triggers, and maybe 2k lines of library code in Python/Go to make the ergonomics a little bit easier on the developer.
andrewflnr 7y ago
I don't understand the distinction between "valid" and "could have been used for other transactions". Can you elaborate and/or give an example? Is one of them always a subset of the other?
- gen220 7y ago
  Happy to! It's not impossible for one instance to be a subset of the other. They can also be mutually exclusive periods of time. Let's do an example to explore Let's say that the present concept of John's address is "123 Apple St"; this was inserted into the database at 1/1/2020. We don't have credible evidence for what John's address was prior to 1/1/2020. Therefore, the row looks like this: > (address:"123 Apple St", valid_period:[1/1/2020,+inf), transaction_period:[1/1/2020,+inf)) This is a case where transaction period and valid period are equal. If John sent us this information by mail, and he signed it 12/25/2019, we have credible evidence that this was John's address, at least effective 12/25/2019. Therefore, his row would look like this: (address:"123 Apple St", valid_period:[12/25/2019,+inf), transaction_period:[1/1/2020,+inf)). Now, the transaction period is still 1/1/2020, because this information found it's way into the database on 1/1/2020. Now, let's continue with the second scenario. Let's say we get a second letter from John, processed on 6/1/2020, saying that he moved to "456 Orange St" on 5/1/2020. There are now two rows in the database, as below: > (address:"123 Apple St", vp:[12/25/2019,5/1/2020), tp:[1/1/2020,+inf)) > (address:"456 Orange St", vp:[5/1/2020,+inf), tp:[6/1/2020,+inf)) Then, we receive a FINAL letter from John, processed on 7/1/2020, revealing that his previous letter contained a typo! it was "789 Orange St", not "456"! Darn. Our table now contains three rows: > (address:"123 Apple St", vp:[12/25/2019,5/1/2020), tp:[1/1/2020,+inf)) > (address:"456 Orange St", vp:[5/1/2020,+inf), tp:[6/1/2020,7/1/2020)) > (address:"456 Orange St", vp:[5/1/2020,+inf), tp:[7/1/2020,+inf)) Let me know if you have any questions about this example!
  - andrewflnr 7y ago
    Ok, so the validity period tries to describe the real world, while the tp is more about the state of the database's knowledge, or rather belief. I think I understand how each one starts when it does, but not when you update the endings. - Does the validity period of the erroneous entry ever get closed? - Or the transaction period of the one that got superseded? - Do transaction periods close for reasons other than finding out something was wrong, or can I think of them as the period that the rest of the row (including validity) is/was believed? Thanks for the detailed explanation. This is really interesting.

by gen220 7y ago

andrei_says_ 7y ago

I imagine you also save changes to the address as separate versions so you can query them in the future?

gen220 7y ago
Yes. In practice, the typical "UPDATE" looks like this: 1. create a "hole" in the current transaction space's valid space (through expirations of records in transaction time, valid time). 2. insert the new version at (transaction_period=(now, inf), valid_period=(effective_date, inf)). It sounds complicated, and it sometimes is, but in practice most of the hard work (specifically around transaction periods) is performed for you by triggers. Usually you only have to think about valid periods, as a developer, which is easier to wrap your mind around. The unfortunate thing is that there aren't a lot of very straightforward open source implementations of bitemporal triggers. The ones that exist are mostly designed to be theoretically sound and feature-complete, rather than usable. At my company, we have our own implementation that has slowly grown over the years. It's about 500 lines of SQL triggers, and maybe 2k lines of library code in Python/Go to make the ergonomics a little bit easier on the developer.

andrewflnr 7y ago

I don't understand the distinction between "valid" and "could have been used for other transactions". Can you elaborate and/or give an example? Is one of them always a subset of the other?

gen220 7y ago
Happy to! It's not impossible for one instance to be a subset of the other. They can also be mutually exclusive periods of time. Let's do an example to explore Let's say that the present concept of John's address is "123 Apple St"; this was inserted into the database at 1/1/2020. We don't have credible evidence for what John's address was prior to 1/1/2020. Therefore, the row looks like this: > (address:"123 Apple St", valid_period:[1/1/2020,+inf), transaction_period:[1/1/2020,+inf)) This is a case where transaction period and valid period are equal. If John sent us this information by mail, and he signed it 12/25/2019, we have credible evidence that this was John's address, at least effective 12/25/2019. Therefore, his row would look like this: (address:"123 Apple St", valid_period:[12/25/2019,+inf), transaction_period:[1/1/2020,+inf)). Now, the transaction period is still 1/1/2020, because this information found it's way into the database on 1/1/2020. Now, let's continue with the second scenario. Let's say we get a second letter from John, processed on 6/1/2020, saying that he moved to "456 Orange St" on 5/1/2020. There are now two rows in the database, as below: > (address:"123 Apple St", vp:[12/25/2019,5/1/2020), tp:[1/1/2020,+inf)) > (address:"456 Orange St", vp:[5/1/2020,+inf), tp:[6/1/2020,+inf)) Then, we receive a FINAL letter from John, processed on 7/1/2020, revealing that his previous letter contained a typo! it was "789 Orange St", not "456"! Darn. Our table now contains three rows: > (address:"123 Apple St", vp:[12/25/2019,5/1/2020), tp:[1/1/2020,+inf)) > (address:"456 Orange St", vp:[5/1/2020,+inf), tp:[6/1/2020,7/1/2020)) > (address:"456 Orange St", vp:[5/1/2020,+inf), tp:[7/1/2020,+inf)) Let me know if you have any questions about this example!
- andrewflnr 7y ago
  Ok, so the validity period tries to describe the real world, while the tp is more about the state of the database's knowledge, or rather belief. I think I understand how each one starts when it does, but not when you update the endings. - Does the validity period of the erroneous entry ever get closed? - Or the transaction period of the one that got superseded? - Do transaction periods close for reasons other than finding out something was wrong, or can I think of them as the period that the rest of the row (including validity) is/was believed? Thanks for the detailed explanation. This is really interesting.