Horton

Re: SQL:2011 application time

Paul A Jungwirth <pj@illuminatedcomputing.com>

View thread
From: Paul A Jungwirth <pj@illuminatedcomputing.com>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: Jaime Casanova <jcasanov@systemguards.com.ec>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2021-11-21T01:51:16Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Rename gist stratnum support function

  2. Remove support for temporal RESTRICT foreign keys

  3. Cache NO ACTION foreign keys separately from RESTRICT foreign keys

  4. Fix NO ACTION temporal foreign keys when the referenced endpoints change

  5. Improve whitespace in without_overlaps test

  6. Tests for logical replication with temporal keys

  7. Support for GiST in get_equal_strategy_number()

  8. Make the conditions in IsIndexUsableForReplicaIdentityFull() more explicit

  9. Replace get_equal_strategy_number_for_am() by get_equal_strategy_number()

  10. Improve internal logical replication error for missing equality strategy

  11. Simplify IsIndexUsableForReplicaIdentityFull()

  12. Fix ALTER TABLE / REPLICA IDENTITY for temporal tables

  13. doc: Update pg_constraint.conexclop docs for WITHOUT OVERLAPS

  14. doc: Add PERIOD to ALTER TABLE reference docs

  15. doc: Add WITHOUT OVERLAPS to ALTER TABLE reference docs

  16. Add temporal FOREIGN KEY contraints

  17. Add temporal PRIMARY KEY and UNIQUE constraints

  18. Add stratnum GiST support function

  19. Avoid crashing when a JIT-inlined backend function throws an error.

  20. Revert temporal primary keys and foreign keys

  21. Fix ON CONFLICT DO NOTHING/UPDATE for temporal indexes

  22. Add test for REPLICA IDENTITY with a temporal key

  23. Use half-open interval notation in without_overlaps tests

  24. Use daterange and YMD in without_overlaps tests instead of tsrange.

  25. Rename pg_constraint.conwithoutoverlaps to conperiod

  26. Fix comment on gist_stratnum_btree

  27. Add missing TAP test name

  28. Improve error handling of HMAC computations

  29. Rename functions to avoid future conflicts

Attachments

On Tue, Nov 16, 2021 at 3:55 PM Paul Jungwirth <pj@illuminatedcomputing.com>
wrote:

> I haven't made any substantive changes, but I should have time soon to
> take a stab at supporting partitioned tables and removing some of my own
> TODOs (things like making sure I'm locking things correctly).
>

Hello,

Here are updated patches. They are rebased and clean up some of my TODOs.
Here is what remains:

- Various TODOs asking for advice about concurrency things: where to lock,
when to copy structs, etc. I'd appreciate some review on these from someone
more experienced than me.

- Supporting FOR PORTION OF against updateable views. I'll keep working on
this, but I thought there was enough progress to pass along new patches in
the meantime.

- Support partitioned tables. I think this is a medium-size effort, and I'm
not sure whether it's really needed for pg 15 or something we can add
later. I'm going to do my best to get it done though. (I should have more
time for this project now: having a sixth baby recently made side projects
challenging for a while, but lately things have been getting easier.)
Partitioning could use some design discussion though, both for application
time alone and for bitemporal tables (so overlapping with the system time
work). Here are some thoughts so far:

  - Creating a PERIOD on a partitioned table should automatically create
the PERIOD (and associated constraints) on the child tables. This one seems
easy and I'll try to get it done soon.

  - Sort of related, but not strictly partitioning: CREATE TABLE LIKE
should have a new INCLUDING PERIODS option. (I'm tempted to include this
under INCLUDING CONSTRAINTS, but I think a separate option is nicer since
it gives more control.)

  - If you partition by something in the scalar part of the temporal PK,
that's easy. I don't think we have to do anything special there. I'd like
to add some tests about it though.

  - We should allow temporal primary keys on the top-level partitioned
table, even though they are essentially exclusion constraints. Whereas in
the general case an exclusion constraint cannot prove its validity across
all the tables, a temporal PK *can* prove its validity so long the
partition key includes at least one scalar part of the temporal PK (so that
all records for one "entity" get routed to the same table).

  - If you partition by the temporal part of the temporal PK, things are
harder. I'm inclined to forbid this, at least for v15. Suppose you
partition by the start time. Then you wind up with the same entity spread
across several tables, so you can't validate the overall exclusion
constraint anymore.

  - OTOH you *could* partition by application-time itself (not start time
alone nor end time alone) where each partition has application-time
ranges/periods that are trimmed to fit within that partition's limits. Then
since each partition is responsible for a non-overlapping time period, you
could validate the overall exclusion constraint. You'd just have to add
some logic to tuple re-routing that could transform single records into
multiple records. For example if each partition holds a different year and
you INSERT a record that is valid for a decade, you'd have to insert one
row into ten partitions, and change the application-time range/period of
each row appropriately. This is a special kind of range partitioning. I
don't have any ideas how to make hash or list partitioning work on the
temporal part of the PK. I don't think we should allow it.

  - Partitioning by application time requires no special syntax.
Partitioning by system time (if that's desired) would probably require
extra (non-standard) syntax. Mariadb has this:
https://mariadb.com/kb/en/system-versioned-tables/#storing-the-history-separately
Perhaps that is orthogonal to application-time partitioning though. It
sounds like people think we should store non-current system time in a
separate table (I agree), and in that case I think a bitemporal table that
is partitioned by scalar keys or application-time would just have a
separate system-time history table for each partition, and that would Just
Work. And if we *do* want to partition by system time too, then it would be
transparent to the application-time logic.

  - Since system time doesn't add anything to your PK (or at least it
shouldn't), there is no extra complexity around dealing with exclusion
constraints. We should just guarantee that all *current* rows land in the
same partition, because for a bitemporal table that's the only one that
needs a temporal PK. I guess that means you could partition by end
system-time but not start system-time. This would be an exception to the
rule that a PK must include the partition keys. Instead we'd say that all
current (i.e. non-historical) records stay together (at the system-time
level of partitioning).

  - I don't think system-time partitioning needs to be in v15. It seems
more complicated than ordinary partitioning.

Yours,
Paul