> What happens if you have, say, two rapid-fire transactions updating the order, with another transaction updating one of the parts sandwiched between the two order updates?
You have a race condition here, regardless of whether you use history tables or not, no? It'll probably still be wrong for the user if you update the part first, then do the two transactions, as you now refer to the wrong (a more recent) part version.
I mean, that's the entire reason why Stripe have `products` with multiple `prices` that are "immutable": The price, tax details and so on can't be changed, but they can be archived or set as default. A line item will refer to the price itself, not the product.
If you somehow need to do multiple transactions and your parts aren't immutable, I think you must refer to some immutable history table -- regardless of whether you use the triggers, make an append only table or do something else. If you use the triggers mentioned in the post, you could save the time you looked at the part and do an insert like so:
INSERT INTO line_items (order, part_id, part_history_id)
SELECT ${order_id}, ${part_id}, ph.history_id
FROM parts_history ph
WHERE ph.part_id = ${part_id}
AND ph.systime @> ${as_of_time};
But to be fair, it seems unnecessarily risky. I'd just query the history as of now and use the history ID in the subsequent transaction, or make an append-only part version table and refer to that.
You have a race condition here, regardless of whether you use history tables or not, no? It'll probably still be wrong for the user if you update the part first, then do the two transactions, as you now refer to the wrong (a more recent) part version.
I mean, that's the entire reason why Stripe have `products` with multiple `prices` that are "immutable": The price, tax details and so on can't be changed, but they can be archived or set as default. A line item will refer to the price itself, not the product.
If you somehow need to do multiple transactions and your parts aren't immutable, I think you must refer to some immutable history table -- regardless of whether you use the triggers, make an append only table or do something else. If you use the triggers mentioned in the post, you could save the time you looked at the part and do an insert like so:
But to be fair, it seems unnecessarily risky. I'd just query the history as of now and use the history ID in the subsequent transaction, or make an append-only part version table and refer to that.