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