Temporal Reverted

My work adding temporal primary keys and foreign keys to Postgres was reverted from v17. The problem is empty ranges (and multiranges). An empty range doesn’t overlap anything, including another empty range. So 'empty' && 'empty' is false. But temporal PKs are essentially an exclusion constraint using (id WITH =, valid_at WITH &&). Therefore you can insert duplicates, as long as the range is empty:

INSERT INTO t (id, valid_at, name) VALUES (5, 'empty', 'foo');
INSERT INTO t (id, valid_at, name) VALUES (5, 'empty', 'bar');

That might be okay for some users, but it surely breaks expectations for others. And it’s a questionable thing to do that we should probably just forbid. The SQL standard forbids empty PERIODs, so we should make sure that using plain ranges does the same. Adding a record with an empty application time doesn’t really have a meaning in the temporal model.

I think this is a pretty small bump in the road. At the Postgres developers conference we found a good solution to excluding empty ranges. My original attempt used CHECK constraints, but that had a lot of complications. Forbidding them in the executor is a lot simpler. I’ve already sent in a new set of patches for v18 that implement that change.

blog comments powered by Disqus Prev: Debugging the Sprinkler System Next: k3s Behind Nginx