Comparing changes

This section introduces temporal tables, with a focus on Application Time (which we support) and only a brief mention of System Time (which we don't). It covers temporal primary keys and unique constraints. Temporal foreign keys are documented in the next commit. We will document temporal update/delete and periods as we add those features. This commit also adds glossary entries for temporal table, application time, and system time. Author: Paul A. Jungwirth <[email protected]>

Author: Paul A. Jungwirth <[email protected]>

The FOR PORTION OF syntax will also be documented in the reference pages for UPDATE and DELETE, but this commit adds a new section to the DML chapter, called "Updating and Deleting Temporal Data," giving a conceptual description, as well as a glossary term for "temporal leftovers". The SQL standard doesn't give any term for the supplementary INSERTs after an UPDATE/DELETE FOR PORTION OF, but it is really handy to have a name for them. Author: Paul A. Jungwirth <[email protected]>

The existing range_minus function raises an exception when the range is "split", because then the result can't be represented by a single range. For example '[0,10)'::int4range - '[4,5)' would be '[0,4)' and '[5,10)'. This commit adds new set-returning functions so that callers can get results even in the case of splits. There is no risk of an exception for multiranges, but a set-returning function lets us handle them the same way we handle ranges. Both functions return zero results if the subtraction would give an empty range/multirange. The main use-case for these functions is to implement UPDATE/DELETE FOR PORTION OF, which must compute the application-time of "temporal leftovers": the part of history in an updated/deleted row that was not changed. To preserve the untouched history, we will implicitly insert one record for each result returned by range/multirange_minus_multi. Using a set-returning function will also let us support user-defined types for application-time update/delete in the future. Author: Paul A. Jungwirth <[email protected]>

- Added bison support for FOR PORTION OF syntax. The bounds must be constant, so we forbid column references, subqueries, etc. We do accept functions like NOW(). - Added logic to executor to insert new rows for the "temporal leftover" part of a record touched by a FOR PORTION OF query. - Documented FOR PORTION OF. - Added tests. Author: Paul A. Jungwirth <[email protected]>

Concurrent updates/deletes in READ COMMITTED mode don't give you what you want: the second update/delete fails to leftovers from the first, so you essentially have lost updates/deletes. But we are following the rules, and other RDBMSes give you screwy results in READ COMMITTED too (albeit different). One approach is to lock the history you want with SELECT FOR UPDATE before issuing the actual UPDATE/DELETE. That way you see the leftovers of anyone else who also touched that history. The isolation tests here use that approach and show that it's viable.

This needs to be passed to our RI triggers to implement temporal CASCADE/SET NULL/SET DEFAULT when the user command is an UPDATE/DELETE FOR PORTION OF. The triggers will use the FOR PORTION OF bounds to avoid over-applying the change to referencing records. Probably it is useful for user-defined triggers as well, for example auditing or trigger-based replication. Author: Paul A. Jungwirth <[email protected]>

To implement CASCADE/SET NULL/SET DEFAULT on temporal foreign keys, we need an intersect function and a minus set-returning function. We can look them up when we look up the operators already needed for temporal foreign keys (including NO ACTION constraints). Author: Paul A. Jungwirth <[email protected]>

Previously we raised an error for these options, because their implementations require FOR PORTION OF. Now that we have temporal UPDATE/DELETE, we can implement foreign keys that use it. Author: Paul A. Jungwirth <[email protected]>

It is helpful for triggers to see what the FOR PORTION OF clause specified: both the column/period name and the targeted bounds. Our RI triggers require this information, and we are passing it as part of the TriggerData struct. This commit allows plpgsql trigger functions to access the same information, using the new TG_PERIOD_COLUMN and TG_PERIOD_TARGET variables. Author: Paul A. Jungwirth <[email protected]>

- Added parsing for SQL:2011 syntax to define an application-time PERIOD on a table (in both CREATE TABLE and ALTER TABLE). Make sure we create the PERIOD after columns are known (since PERIODs can refer to them) but before constraints are handled (since PERIODs can appear in them). - Added ALTER TABLE DROP support for PERIODs. - Created postgres.pg_period table. - Created information_schema.periods view. - Added pg_dump support. - Added tests and documentation. - Automatically define a constraint for each PERIOD requiring the start column to be less than the end column. - When creating a PERIOD, choose an appropriate range type we can use to implement PERIOD-related operations. You can choose one explicitly if there is ambiguity (due to multiple range types created over the same base type). Author: Paul Jungwirth

This branch was automatically generated by a robot using patches from an email thread registered at: https://commitfest.postgresql.org/patch/5836 The branch will be overwritten each time a new patch version is posted to the thread, and also periodically to check for bitrot caused by changes on the master branch. Patch(es): https://www.postgresql.org/message-id/CA+renyUodzxAvMnpa_LTvo+Ru1ZKH+Su8KaPvD4iMtguFKzq4g@mail.gmail.com Author(s): Paul Jungwirth

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Comparing changes

Open a pull request

Commits on Nov 5, 2025

This comparison is taking too long to generate.

Uh oh!