ApplicationTimeProgress

Introduction

This is a design document for application-time temporal features in Postgres. For a general introduction to SQL:2011 temporal features, see SQL2011Temporal.

One design goal of all our temporal features is to support Postgres range types and multirange types anywhere the SQL:2011 standard permits PERIODs. We plan to support PERIODs as well. In fact in keeping with Postgres's history of extensibility, we should allow any user-defined type, as long as it provides the needed functions/operators.

Progress

• Primary Keys and Unique Constraints
  • With ranges & multiranges: DONE, v18: fc0438b4e80535419a4e54dba87642cdf84defda. This was originally included in v17, but we had to revert because we didn't forbid empty ranges. With ranges, 'empty' && foo is always false, even 'empty' && 'empty' (likewise with multiranges). Therefore an exclusion constraint will permit duplicates. Duplicates mess up several things, like inferring functional dependency on the primary key in a GROUP BY, REPLICA IDENTITY in logical replication, and probably some optimizations in the planner. A temporal primary key should be at least as restrictive as a non-temporal primary key (i.e. it also forbids duplicates). So empty ranges had to be dealt with. We added some logic when checking exclusion constraints to forbid empties if it's a temporal primary key or unique constraint.
  • With user-defined types: not yet.
  • On partitioned tables: supported. This required first allowing (some) exclusion constraints on partitions in 8c852ba9a4.
  • pg_dump/pg_restore: done.
  • As a REPLICA IDENTITY in logical replication: supported.

• Foreign Keys: NO ACTION
  • With ranges & multiranges: DONE, v18: 89f908a6d0ac1337c868625008c9598487d184e7
  • With user-defined types: not yet.
  • On partitioned tables: supported.
  • pg_dump/pg_restore: done.

• Foreign Keys: RESTRICT
  • We had a disagreement about how RESTRICT should behave, so we deferred this until later. (See below for more.)

• UPDATE/DELETE FOR PORTION OF
  • With ranges and multiranges: in development, see commitfest.
  • With user-defined types: not yet.
  • Against an updateable view: supported.
  • In logical replication: supported. Replication doesn't see the FOR PORTION OF, but gets an UPDATE/DELETE plus one INSERT for each temporal leftover.
  • Against partitioned tables: supported.
  • Against an FDW: not yet supported. Do we want some kind of announcement or migration for FDWs? If we just send them the plan with or without a ForPortionOfExpr, and they haven't been updated, then they will apply the wrong update/delete. Maybe that is okay, although we should at least do the right thing in postgres_fdw.

• Foreign Keys: CASCADE, SET NULL, SET DEFAULT
  • In development, see commitfest.
  • Once we support temporal update/delete, these are easy to implement, because they use a temporal update/delete to apply these changes.

• PERIODs
  • In development, see commitfest.
  • CREATE TABLE
  • ALTER TABLE ADD PERIOD
  • ALTER TABLE DROP PERIOD
  • Used in a primary key or unique constraint.
  • Used in a foreign key.
  • Used in UPDATE/DELETE FOR PORTION OF.
  • Comments on a PERIOD: not yet.
  • With LIKE: not copied. I don't think the standard says we should copy them, so we don't. But I only have a draft standard from 2011.
  • With table inheritance: not supported
  • On partitions: not supported
  • User-defined types are not an issue because a period doesn't make sense as anything but a range. However we *can* support any base type for the range, despite the standard only requiring date, timestamp and timestamptz.

Primary Keys and Unique Constraints

A temporal primary key or unique constraint contains one or more scalar columns, plus a range/multirange/PERIOD.

Temporal primary keys and unique constraints are implemented as exclusion constraints backed by GiST indexes, containing one or more scalar key parts plus a final range/multirange column representing the start/end of application time. If the constraint is (id, valid_at WITHOUT OVERLAPS) then the exclusion constraint is EXCLUDE USING gist (id WITH =, valid_at WITH &&).

In practice this obliges users to CREATE EXTENSION btree_gist, since there is no built-in opclass for common scalar key types like int, bigint, or text. This is not unique to temporal constraints: the same requirement exists for any exclusion constraint that is not trivally replaceable with a btree unique constraint.

We didn't want to hardcode = and &&, so we look up the operators using strategy numbers. But GiST strategy numbers are not fixed: each opclass can choose its own. So we introduced an opclass support function to let the opclass tell us the correct strategy number for a given "compare type". See 32edf732, 630f9a43cece93cb4a5c243b30e34abce6a89514, 7406ab623fee1addcb21c881afecbe638a0d56e9.

We include special handling for empty ranges/multiranges. If a temporal primary key or unique constraint is present, empty values in its application-time column are forbidden. Such values have no meaning in temporal theory (the row is true "for no time"). As PERIODs they would be forbidden, since a PERIOD's start must strictly precede its end. In addition they would permit duplicates: for ranges and multiranges 'empty' && anything is false, even 'empty' && 'empty', and therefore the exclusion constraint will always pass. But a primary key with duplicate records confuses other parts of Postgres, such as functional dependency detection in GROUP BY and applying updates/deletes in logical replication.

Note that we forbid empty values not just for primary keys, but unique constraints as well. (NULL values in a unique constraint are permitted, like the scalar parts.) SQL:2011 has no concept of a null PERIOD, and a row that is asserted for a NULL or empty span of history has no meaning.

Although SQL:2011 PERIODs only support a base type of date, timestamp, or timestamptz, Postgres range types support any base type. There is no reason to restrict temporal constraints to just daterange, tsrange, and tstzrange. (We could still restrict PERIODs if we like.)

Support for temporal primary key and unique constraints was added by fc0438b4e80535419a4e54dba87642cdf84defda.

Temporal constraints are supported across partitioned tables. This was enabled by 8c852ba9a4347c4778cc610ad5a9cb50ea701b5c.

Temporal constraints can be used as the REPLICA IDENTITY for logical replication (as of 79b575d3bc09cc67a7b5c2da454aeb35f4beccc5). Note that a temporal constraint is always at least as restrictive as its non-temporal analogue. The key uniquely identifies a record.

We do not yet support arbitrary types, only ranges and multiranges. Other types that might make sense here include box, polygon, circle, inet, and spatial types. We would require other types to have an overlaps operator. In addition types will need a way to exclude "empty" values. Probably this is an opclass support function. We should document some formalization of what a type needs to guarantee. In particular it has to ensure that when used in an exclusion constraint, the overlaps operator and this non-empty function ensure that the constraint prevents all duplicates.

Foreign Keys

Under a temporal foreign key, a referencing row is valid if all its history is covered by matching row(s) from the referenced table. Note that more than one row may be required. Temporal foreign keys find all matching rows in the referenced table (rows where the scalar key part is equal and the application-time part overlaps) and use range_agg to combine their histories into one value. If that value contains the referencing application-time, the reference is valid.

Support for ranges and multiranges was added by 89f908a6d0ac1337c868625008c9598487d184e7.

We do not yet support arbitrary types, only ranges and multiranges. Other types would require:

• An overlaps operator (like range/multirange &&).
• An intersect operator (like range/multirange *).
• An aggregate function to combine values (like range_agg).
• A symmetrical contained-by operator (like range <@ range).
• A contained-by operator whose rhs type matches the above aggregate function's return type (like range <@ multirange).
• In addition, the actions CASCADE, SET NULL, and SET DEFAULT require everything required by temporal update/delete.

We don't have agreement on the correct behavior of RESTRICT foreign keys. If a referenced row's range shrinks, but not enough to orphan any references, should a RESTRICT constraint raise an error? What if the referenced row grows? See this thread for more discussion.

It would be helpful to see how others have interpreted the standard. No other RDBMS implements temporal foreign keys yet. IBM DB2 claims to, but in all my tests declaring a temporal foreign key is just a syntax error. Vik Fearing's periods extension treats RESTRICT foreign keys the same as NO ACTION, except they are checked immediately, before a different row can supplement the potentially-lost history. Hettie Dombrovskaya's pg_bitemporal extension does not distinguish between NO ACTION and RESTRICT foreign keys.

Since we don't have consensus, we have decided to leave this unimplemented for now.

UPDATE/DELETE FOR PORTION OF

I give both the standard syntax FOR PORTION OF valid_at FROM x TO y and the alternate syntax FOR PORTION OF valid_at (x). The latter is necessary for multiranges and (potentially) user-defined types.

Supporting the FROM x TO y syntax was tricky because of some shift-reduce conflicts with INTERVAL. I wrote article about that.

I originally implemented temporal update/delete with triggers, but now I do it in the executor/nodeModifyTable.c. That seems like a simpler approach, and I think it is necessary to avoid problems in READ COMMITTED mode.

Getting all the triggers to fire in the right order and with the right transition tables seemed hard at first, so for a while I was using SPI. But reviewers pointed out many reasons to avoid SPI, and it turns out that doing the right thing with triggers is not so hard after all.

We could support any user-defined type that provides these things:

• An Overlaps operator.
• An Intersect operator. This lets us set the new start/end bounds when a record is updated. The bounds are old * target. Note that Intersect doesn't have a StrategyNumber and can't appear in pg_amop, because it has an amoppurpose of neither s (searching) nor o (ordering). In fact it is not related to an index at all, so it doesn't appear in any opclass. Perhaps we would introduce a type support function concept (as proposed but not implemented for Expanded Objects in PL/pgSQL and (I think) alternate units windows functions' frame positions). Types have many helper functions already; we just don't have a concept for handling them in general.
• A "minus" set-returning function. This lets us set the start/end bounds of temporal leftovers. Their bounds are old - target. It needs to be a SRF because a range (or other type) might have more than one leftover. There is a separate patch in the series to add these SRFs, called range_minus_multi and multirange_minus_multi. A user-defined type would need to provide a similar function and point to it somehow.

Each inserted temporal leftover fires all insert triggers (statement+row, before+after). For discussion see the mailing list.

A temporal update/delete adds the targeted portion to the TriggerData struct. This is needed for temporal foreign keys with CASCADE/SET NULL/SET DEFAULT. I also have a patch exposing it to PL/pgSQL triggers. It is not exposed to SQL triggers yet. The SQL standard suggests exposing it to triggers in the future, but it doesn't specify anything yet.

PERIODs

These are implemented by creating a GENERATED range column (of the same name) and making it hidden from SELECT * and \d table_name. Then when you use the period in a temporal constraint or update/delete, it works with that range column.

Postgres allows users to create multiple rangetypes with the same base type, and we have to infer a rangetype given the start/end columns' types. If there is no ambiguity, we just use the only choice. If there is ambiguity, we raise an error. But the user can supply a rangetype option when creating the period to tell us which one to use.

We also support a colexists option when creating the period, to avoid adding a new generated column. This is used by pg_dump.

I added a new entry to the AlterTablePass enum, because adding periods has to come after columns (which they reference) and before constraints (which reference them).

There was some trickiness about tracking dependencies, because of how the dependency system treats column dependencies as an object sub-id. The patch includes a fix for this in catalog/dependency.c.

Future Work

Once we support all of SQL:2011, here are things to consider that go beyond the standard:

• Support the above with arbitrary types.
• Temporal upsert/merge.
• Temporal outer join.
• Temporal antijoin.
• Temporal semijoin.
• Temporal UNION, EXCEPT, INTERSECT.
• Temporal aggregates.