Since there have been a couple threads on the hackers list about temporal features [1, 2], I thought I'd share an extension I've been writing for temporal foreign keys: https://github.com/pjungwir/time_for_keys There is a big test suite, but right now it is still basically a proof-of-concept, restricted to single-column-integer+tstzrange, implemented mostly in plpgsql, with no extra support when a table/column is dropped/renamed. I'd like to add support for: - multi-column foreign keys - other FK types than integers - other range types than tstzrange (at least tsrange, but maybe any range) - better catalog integration: show one constraint instead of four constraint triggers, properly restrict/cascade when a table/column is dropped, make the triggers work when a table/column is renamed. - support ON UPDATE/DELETE CASCADE/SET NULL/SET DEFAULT - I think I also need to add FOR KEY SHARE locking to get correct concurrent behavior. I think all that would be easier if I rewrote the plpgsql parts in C. I've been reading the code in commands/tablecmds.c, catalog/pg_constraint.c, utils/adt/ri_triggers.c, catalog/dependency.c, etc., and I think I'm ready to get started. I think I'd be doing a lot of copy/paste/adjust from the normal RI code. But first I wanted to share what I have so far and see if anyone could offer advice or feedback. Some other extensions that have been helpful for me to read are: - https://github.com/arkhipov/temporal_tables - https://github.com/CartoDB/pg_schema_triggers for detecting DROP/ALTER commands Right now this is an extension, but I'd be honored to contribute it to the core project eventually if folks want that. One issue there is that (traditional) foreign keys require indexes, and you can't index a thing+range without btree_gist, another extension. Oh, sorry if this belongs on the hackers list instead of general. I wasn't really sure which was best! Thanks, Paul [1] https://www.postgresql.org/message-id/CALNdv1h7TUP24Nro53KecvWB2kwA67p%2BPByDuP6_1GeESTFgSA%40mail.gmail.com [2] https://www.postgresql.org/message-id/78aadf6b-86d4-21b9-9c2a-51f1efb8a499%40postgrespro.ru
