Temporal foreign key actions

Dear Hackers,

Here is a patch series to add RESTRICT, CASCADE, SET NULL, and SET
DEFAULT temporal foreign keys.

The first two patches are small preliminary details that would let me
do everything else in an extension, so I would love to get those into
v20 at least. But having all FK actions in core would be even better.

The first patch teaches ExecForPortionOfLeftovers to obey
EXEC_FLAG_SKIP_TRIGGERS (arguably fixing a bug, although not one that
is currently reachable). The second adds FOR PORTION OF details to
TriggerData. This is needed for temporal FK actions, and several
people who tested FOR PORTION OF for v19 gave remarks suggesting these
would be useful for them too.

Patches 3 & 4 are also preliminary work, looking up another operator
and adding a parameter to ri_PerformCheck. They needn't be committed
separately, but I think they make everything easier to review.

Patch 5 adds RESTRICT. I think I have enough information now to
hopefully get consensus on how RESTRICT should work. Markus Winand was
kind enough to run some test queries against a z/OS DB2 instance. On
Linux DB2, temporal FKs are just a syntax error, but on z/OS they
really work. That plus a section of the standard I overlooked before
both confirm that RESTRICT should only fail if a *referenced moment*
changes. See below for details. Since this is part of the standard, I
think it makes sense in core.

Patches 6 & 7 add CASCADE and SET NULL/SET DEFAULT. This is not yet
standardized, but I think the correct behavior is clear. I'm
interested in getting this into Postgres and also adding it to the
standard. One principle is that RESTRICT should fail iff CASCADE would
change a referencing row. I had Claude help me make a webpage to
exhaustively diagram the different cases:

https://github.com/pjungwir/temporal-fk-actions/blob/main/temporal-fk-actions.md

This follows the standard temporal model described unanimously by
Snodgrass, Date/Darwen/Lorentzos, and Johnston/Weis: the temporal
interval should be equivalent to a bunch of separate rows, one for
each moment.

Hopefully we can add this too, but I'll understand if people are
reluctant. I can do it as an extension if the first two patches here
get merged.

Here are some details about how RESTRICT should behave:

DB2 supports ON DELETE RESTRICT, and it only fails if a referenced
*moment* changes. (DB2 doesn't have separate ON UPDATE actions;
updates just use the delete action.[1]) If a referenced row changes,
but the change is confined to unreferenced parts of history, then the
command succeeds. I've attached the full test script, but here are
some notable changes that succeeded:

```
create table products ( id integer not null, ds date not null, de date
not null, name varchar(255) not null, period business_time (ds, de),
constraint pk_products primary key (id, business_time without
overlaps));
create table variants ( id integer not null, ds date not null, de date
not null, product_id integer not null, period business_time (ds, de),
constraint pk_variants primary key (id, business_time without
overlaps), constraint fk_variants_on_product_id foreign key
(product_id, period business_time) references products (id, period
business_time) on delete restrict);
insert into products (id, ds, de, name) values (10, '2000-01-01',
'2001-01-01', '10'), (10, '2001-01-01', '2002-01-01', '10'), (20,
'2000-01-01', '2001-01-01', '20'), (30, '2000-01-01', '2001-01-01',
'30'), (40, '2000-01-01', '2001-01-01', '40');
insert into variants (id, ds, de, product_id) values (10,
'2000-03-01', '2000-04-01', 10), (30, '2000-03-01', '2000-04-01', 30),
(40, '2000-03-01', '2000-04-01', 40);
-- Deleting an unreferenced *part* of application-time should succeed:
-- WORKED:
delete from products for portion of business_time from '2000-05-01' to
'2000-06-01' where id = 30;
-- Deleting a referenced *part* of application-time should fail:
-- FAILED:
delete from products for portion of business_time from '2000-03-01' to
'2000-03-02' where id = 40;
-- ...
-- Then with new tables
-- ...
-- Updating an unreferenced *part* of application-time should succeed...
-- ...with FOR PORTION OF
-- WORKED:
update products
for portion of business_time from '2000-05-01' to '2000-06-01'
set id = -id
where id = 30;
-- ...with SET
-- but removing referenced history and not removing referenced history.
-- FAILED:
update products
set id = -id,
ds = '2000-01-01',
de = '2000-09-01'
where id = 35
and '2000-06-01' between ds and de;
-- Updating attributes from a referenced *part* of application-time...
-- ...should succeed with FOR PORTION OF:
-- WORKED:
update products for portion of business_time from '2000-03-01' to '2000-03-02'
set name = '-40'
where id = 40;
-- ...and SET is boring:
-- WORKED:
update products
set name = '-45'
where id = 45
and '2000-06-01' between ds and de;
```

The standard also supports this interpretation of RESTRICT. I missed
it before, but it actually gives a direct answer. In Foundations, on
page 899-901 of the 2023 PDF (aka 877-879 of the numbers printed in
the left/right corner), section 11.8 Syntax Rules, rule 18.a.i, there
is this for foreign keys with PERIOD:

9) Let TTS be the <table subquery>
( SELECT TCL, TIMEPOINT1
FROM TNN AS Y,
UNNEST(EXPAND(TSTARTCOL, TENDCOL)) AS X(TIMEPOINT1) )
where EXPAND is the <routine name> of an SQL-invoked function that returns a
value of SET(TDT) type consisting of all values of TDT that are
greater than or
equal to the value of TSTARTCOL and less than the value of TENDCOL, and
TIMEPOINT1 is an <identifier> not equivalent to the <column name>
of any column
of T.
NOTE 553 — The use of the SQL-invoked function invocation above is
only for definitional
purposes in this document.
10) Let UNNN be the <derived table>
( SELECT Z.*, TIMEPOINT2
FROM UNN AS Z,
UNNEST(EXPAND(USTARTCOL, UENDCOL)) AS X(TIMEPOINT2)
) AS Y
where EXPAND is the <routine name> of an SQL-invoked function that returns a
value of SET(UDT) type consisting of all values of UDT that are
greater than or
equal to the value of USTARTCOL and less than the value of UENDCOL, and
TIMEPOINT2 is an <identifier> not equivalent to the <column name>
of any column
of U.
NOTE 554 — The use of the SQL-invoked function invocation above is
only for definitional
purposes in this document.
11) Let UCLL be the <column name list>
UCL, TIMEPOINT2

Then UCLL and TTS are used to define SC: the <search condition> for
the referential action (General Rule 2.g).

That is saying that when you match temporal foreign keys, you EXPAND
them so that each "moment" (from start to end) is notionally a
separate row. This is the same model that every author of temporal
databases has used. The Date book explicitly defines all its
operations in terms of it (called PACK/UNPACK there). The periods are
just an optimization since you can't really have zillions of rows to
cover every moment. This equivalence is the core of the "temporal
semantics" implemented by these features.

Since the standard doesn't include temporal CASCADE/SET NULL/SET
DEFAULT, those sections are purely about RESTRICT---although I think
they are correct for the other actions too.

[1] https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=statements-alter-table#db2z_sql_altertable__syntaxdiagram_ppp_ztw_21c__title__1

Yours,

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