Re: Temporal Table Proposal

On 2/25/19 4:21 AM, Ibrar Ahmed wrote:
> Great, to hear that you are working on that. Do you think I can help you
> with this? I did some groundwork to make it possible. I can help in
> coding/reviewing or even can take lead if you want to.

Hi Ibrar,

I'd love some help with this! I submitted my patch to the March
commitfest, and Peter Moser & Anton Dignös submitted theirs also. I
still need to rebase on the most recent commits, but I'll try to do that
tonight or tomorrow. Personally I'd love some review and feedback,
because this is my first substantial patch. (I made a small change to
btree_gist a couple years ago also....)

I think the challenge with temporal functionality is that there are a
lot of new concepts, and we'd like them all to hang together in a
coherent way. (That's why I want to give a talk about it: to increase
background understanding in the Postgres community.) So having someone
take the lead on it makes sense. I'm happy to provide some opinions and
direction, but my own coding contributions are likely to be slow, and
having a regular contributor more closely involved would help a lot.

Here are some thoughts about things that need work:

- temporal primary keys (my patch)
- temporal foreign keys (I've done some work on this adding to my patch
but I haven't finished it yet.)
- temporal joins (Moser/Dignös patch)
- declaring PERIODs (Vik Fearing's patch)
- showing PERIODs in the system catalog (Vik Fearing's patch)
- using PERIODs in SELECT, WHERE, GROUP BY, HAVING, function arguments,
etc. (TODO)
- SYSTEM_TIME PERIODs for transaction-time tables (TODO)
- temporal insert/update/delete for transaction-time tables (TODO)
- temporal insert/update/delete for valid-time tables (TODO)
- temporal SELECT for valid-time tables (TODO, could build off the
Moser/Dignös work)
- temporal SELECT for transaction-time tables (TODO)

I think the transaction-time stuff is easier, but also less interesting,
and there are well-known patterns for accomplishing it already. I'm more
interested in supporting valid-time tables personally.

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com