[Paul Jungwirth <pj@illuminatedcomputing.com>]

On 11/14/24 09:31, Paul Jungwirth wrote:
 >> Thank you for the report! I confirmed that this is a problem. In ri_restrict we fail if any fk
 >> records still match the being-changed pk, but for temporal if you're merely shrinking the pk
 >> range, fk references could still wind up being valid (if you're only shrinking it a little). So we
 >> need to do more work.
 >
 > I'm nearly done with a patch for this. I'll try to wrap it up today and get it sent this evening.

Here are patches fixing the foreign key problems, as well as the outstanding logical replication fix 
(with more explanation in the commit message). There is also a commit to add the without_portion 
support function (originally intended for FOR PORTION OF, but useful here too).

For NOACTION, we might as well skip ri_Check_Pk_Match, because we need to look up the details of the 
referenced/referencing time periods, and we can do that in the main SQL query below.

On 11/4/24 13:16, Sam Gabrielsson wrote:
 >    SELECT 1
 >    FROM (SELECT range_agg(pkperiodatt) AS r
 >        FROM <pktable>
 >        WHERE pkatt1 = $1 [AND ...]
 >        AND pkperiodatt && $n) AS pktable,
 >      (SELECT fkperiodatt AS r
 >        FROM <fktable>
 >        WHERE fkatt1 = $1 [AND ...]
 >        AND fkperiodatt && $n) AS fktable
 >    WHERE NOT fktable.r <@ pktable.r

This query doesn't quite work, because the FK record can span multiple PK records, so finding only 
PK records that overlap the changed range may miss them. That means we could still fail erroneously.

One fix is to consider *all* PK ranges with the same scalar key part, but that will get expensive.

A better fix is to consider only FK ranges after truncating them to fit within the updated PK span.
We can use the intersect operator for that. Since temporal foreign keys only support ranges & 
multiranges, we can hardcode that operator lookup. (I still hope to support arbitrary types in the 
future, and asking for an intersect operator isn't hard.)

Here is some SQL using that approach to find invalid references. Variables like $1 and $n are from 
oldslot, and $n is the range value (like the normal FK check).

     SELECT 1
     FROM [ONLY] <fktable> x
     WHERE $1 = x.fkatt1 [AND ...] AND $n && x.fkperiod
     AND NOT coalesce((x.fkperiod * $n) <@
      (SELECT range_agg(r)
       FROM (SELECT y.pkperiod r
             FROM [ONLY] <pktable> y
             WHERE $1 = y.pkatt1 [AND ...] AND $n && y.pkperiod
             FOR KEY SHARE OF y) y2), false)
     FOR KEY SHARE OF x

We need the coalesce because the range_agg subquery could return no rows, and `NOT x <@ NULL` is 
null. We need another subquery because FOR KEY SHARE isn't permitted in aggregate queries.

On 11/14/24 09:31, Paul Jungwirth wrote:
 > The RESTRICT case needs to find the *lost* time span(s) (because it might not be the whole thing)
 > and check for references to those. To do that, it calls our without_portion support function. That
 > function was intended to support FOR PORTION OF, but it happens to be exactly what we need here. So
 > I'm reordering the patches a bit and adjusting the documentation there.

To elaborate:

Here is what SQL:2011 says for ON UPDATE RESTRICT (4.18.3.3):

 > ON UPDATE RESTRICT: any change to a referenced column in the referenced table is prohibited if 
there is a matching row.

I think this requires some interpretation for temporal foreign keys. It is not talking about the 
PERIOD part, but about the scalar part(s). (Recall that in the standard the PERIOD is not even a 
column.) It helps to apply the principle that a temporal table behaves the same as a table with one 
row per second (or millisecond or whatever). We should fail if the key changes for a referenced moment.

But we don't get a chance to find replacements in the PK table. Instead of asking whether FKs' 
requirements are still fulfilled, we need to ask what was lost and then fail if we find references 
to that.

There are several cases to consider: Did you change the start/end times? Did you change the scalar 
key part? Did you use FOR PORTION OF?

Assume you didn't use FOR PORTION OF. Let $old and $new indicate the old and new valid-time values. 
If you changed the scalar key part, then all of $old is treated as lost. $new doesn't matter. If you 
didn't change it, then only `$old - $new` is treated as lost. (Note that `$old - $new` could be 
empty---say you expanded the span---meaning nothing was lost here.) If there are any references 
overlapping the lost part(s), we fail.

With FOR PORTION OF things are a little different. IMO even a RESTRICT key should not fail if it 
references "leftovers" that weren't targeted by FOR PORTION OF. It's true that we shrink the 
referenced row, and the reference now depends on the newly-inserted replacements. But conceptually 
the replacements are representing the timeline that *didn't change*. Also if you take the opposite 
position, then FOR PORTION OF is simply unusable with RESTRICT keys.

With that understanding, if you change the scalar key part, the lost part is $new, not $old. And if 
you don't change the scalar key part, nothing is lost at all.

I could be wrong though. I didn't find anything in the standard addressing this specifically, and 
I'm only working from a draft of SQL:2011. If someone with a more current copy has specific 
guidance, I'd be happy to see that.

In any case, FOR PORTION OF isn't merged yet. For now I've only attached patches to fix the 
outstanding problems. After rebasing tonight I ran into some tricky conflicts with my FOR PORTION OF 
and PERIODs patches, so I will re-submit those later. Adapting the RESTRICT code for FOR PORTION OF, 
using the plan above, is pretty simple. We just set $n to $new/empty instead of $old.

Rebased to 818119afcc.

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com

Attachments: download all (4 files, .tar.gz)

v44-0001-Fix-logical-replication-for-temporal-tables.patch (text/x-patch, 29.8K) download
From 510b00dcf881c5c53591fbd73871654d5bf36215 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Wed, 16 Oct 2024 11:06:40 -0700
Subject: [PATCH v44 1/4] Fix logical replication for temporal tables

A WITHOUT OVERLAPS primary key or unique constraint should work as a
REPLICA IDENTITY, since it guarantees uniqueness. But there were two
bugs (both fixed here): (1) REPLICA IDENTITY USING INDEX did not accept
a GiST index. This should be allowed when used as a temporal primary
key. (2) Subscribers applying logical decoding messages got confused
because they tried to look up the equals operator based on btree
strategy numbers. For GiST indexes we can use the stratnum GiST support
function.

Author: Paul Jungwirth
---
 src/backend/commands/tablecmds.c              |  11 +-
 src/backend/executor/execReplication.c        |   9 +-
 .../regress/expected/without_overlaps.out     |  11 +-
 src/test/regress/sql/without_overlaps.sql     |   2 +-
 src/test/subscription/t/034_temporal.pl       | 668 ++++++++++++++++++
 5 files changed, 694 insertions(+), 7 deletions(-)
 create mode 100644 src/test/subscription/t/034_temporal.pl

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ccd9645e7d2..cbd1d9c56fc 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -17244,9 +17244,14 @@ ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKMODE lockmode
 				 errmsg("\"%s\" is not an index for table \"%s\"",
 						RelationGetRelationName(indexRel),
 						RelationGetRelationName(rel))));
-	/* The AM must support uniqueness, and the index must in fact be unique. */
-	if (!indexRel->rd_indam->amcanunique ||
-		!indexRel->rd_index->indisunique)
+	/*
+	 * The AM must support uniqueness, and the index must in fact be unique.
+	 * If we have a WITHOUT OVERLAPS constraint (identified by uniqueness +
+	 * exclusion), we can use that too.
+	 */
+	if ((!indexRel->rd_indam->amcanunique ||
+		!indexRel->rd_index->indisunique) &&
+		!(indexRel->rd_index->indisunique && indexRel->rd_index->indisexclusion))
 		ereport(ERROR,
 				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
 				 errmsg("cannot use non-unique index \"%s\" as replica identity",
diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
index 54025c9f150..152fe3140a0 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -15,6 +15,7 @@
 #include "postgres.h"
 
 #include "access/genam.h"
+#include "access/gist.h"
 #include "access/relscan.h"
 #include "access/tableam.h"
 #include "access/transam.h"
@@ -77,7 +78,11 @@ get_equal_strategy_number(Oid opclass)
 {
 	Oid			am = get_opclass_method(opclass);
 
-	return get_equal_strategy_number_for_am(am);
+	/* For GiST indexes we need to ask the opclass what strategy number to use. */
+	if (am == GIST_AM_OID)
+		return GistTranslateStratnum(opclass, RTEqualStrategyNumber);
+	else
+		return get_equal_strategy_number_for_am(am);
 }
 
 /*
@@ -134,6 +139,8 @@ build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
 		optype = get_opclass_input_type(opclass->values[index_attoff]);
 		opfamily = get_opclass_family(opclass->values[index_attoff]);
 		eq_strategy = get_equal_strategy_number(opclass->values[index_attoff]);
+		if (!OidIsValid(eq_strategy))
+			elog(ERROR, "missing equal strategy for opclass %u", opclass->values[index_attoff]);
 
 		operator = get_opfamily_member(opfamily, optype,
 									   optype,
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index d6cb65e9a63..319662bd58d 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -978,9 +978,16 @@ SELECT * FROM tp2 ORDER BY id, valid_at;
 
 DROP TABLE temporal_partitioned;
 -- ALTER TABLE REPLICA IDENTITY
--- (should fail)
 ALTER TABLE temporal_rng REPLICA IDENTITY USING INDEX temporal_rng_pk;
-ERROR:  cannot use non-unique index "temporal_rng_pk" as replica identity
+\d temporal_rng
+              Table "public.temporal_rng"
+  Column  |   Type    | Collation | Nullable | Default 
+----------+-----------+-----------+----------+---------
+ id       | int4range |           | not null | 
+ valid_at | daterange |           | not null | 
+Indexes:
+    "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) REPLICA IDENTITY
+
 --
 -- ON CONFLICT: ranges
 --
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 943edf3da63..ebee2f2932e 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -691,8 +691,8 @@ SELECT * FROM tp2 ORDER BY id, valid_at;
 DROP TABLE temporal_partitioned;
 
 -- ALTER TABLE REPLICA IDENTITY
--- (should fail)
 ALTER TABLE temporal_rng REPLICA IDENTITY USING INDEX temporal_rng_pk;
+\d temporal_rng
 
 --
 -- ON CONFLICT: ranges
diff --git a/src/test/subscription/t/034_temporal.pl b/src/test/subscription/t/034_temporal.pl
new file mode 100644
index 00000000000..3ef4249a9fe
--- /dev/null
+++ b/src/test/subscription/t/034_temporal.pl
@@ -0,0 +1,668 @@
+
+# Copyright (c) 2021-2024, PostgreSQL Global Development Group
+
+# Logical replication tests for temporal tables
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# setup
+
+my $node_publisher = PostgreSQL::Test::Cluster->new('publisher');
+$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->start;
+
+my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
+$node_subscriber->init;
+$node_subscriber->start;
+
+my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
+
+my ($result, $stdout, $stderr);
+
+# #################################
+# Test with REPLICA IDENTITY DEFAULT:
+# #################################
+
+# create tables on publisher
+
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE temporal_no_key (id int4range, valid_at daterange, a text)"
+);
+
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text, PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE temporal_unique (id int4range NOT NULL, valid_at daterange NOT NULL, a text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+
+# create tables on subscriber
+
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE temporal_no_key (id int4range, valid_at daterange, a text)"
+);
+
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text, PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE temporal_unique (id int4range NOT NULL, valid_at daterange NOT NULL, a text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+
+# sync initial data:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_no_key (id, valid_at, a)
+   VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_pk (id, valid_at, a)
+   VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_unique (id, valid_at, a)
+   VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION pub1 FOR ALL TABLES");
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub1"
+);
+$node_subscriber->wait_for_subscription_sync;
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_no_key ORDER BY id, valid_at");
+is( $result,
+	qq{[1,2)|[2000-01-01,2010-01-01)|a},
+	'synced temporal_no_key DEFAULT');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is($result, qq{[1,2)|[2000-01-01,2010-01-01)|a},
+	'synced temporal_pk DEFAULT');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result,
+	qq{[1,2)|[2000-01-01,2010-01-01)|a},
+	'synced temporal_unique DEFAULT');
+
+# replicate with no key:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_no_key (id, valid_at, a)
+   VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+	"UPDATE temporal_no_key SET a = 'b' WHERE id = '[2,3)'");
+is( $stderr,
+	qq(psql:<stdin>:1: ERROR:  cannot update table "temporal_no_key" because it does not have a replica identity and publishes updates
+HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
+	"can't UPDATE temporal_no_key DEFAULT");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+	"DELETE FROM temporal_no_key WHERE id = '[3,4)'");
+is( $stderr,
+	qq(psql:<stdin>:1: ERROR:  cannot delete from table "temporal_no_key" because it does not have a replica identity and publishes deletes
+HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
+	"can't DELETE temporal_no_key DEFAULT");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_no_key ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|a
+[3,4)|[2000-01-01,2010-01-01)|a
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_no_key DEFAULT');
+
+# replicate with a primary key:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_pk (id, valid_at, a)
+   VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+	"UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'");
+
+$node_publisher->safe_psql('postgres',
+	"DELETE FROM temporal_pk WHERE id = '[3,4)'");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|b
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk DEFAULT');
+
+# replicate with a unique key:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_unique (id, valid_at, a)
+   VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+	"UPDATE temporal_unique SET a = 'b' WHERE id = '[2,3)'");
+is( $stderr,
+	qq(psql:<stdin>:1: ERROR:  cannot update table "temporal_unique" because it does not have a replica identity and publishes updates
+HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
+	"can't UPDATE temporal_unique DEFAULT");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+	"DELETE FROM temporal_unique WHERE id = '[3,4)'");
+is( $stderr,
+	qq(psql:<stdin>:1: ERROR:  cannot delete from table "temporal_unique" because it does not have a replica identity and publishes deletes
+HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
+	"can't DELETE temporal_unique DEFAULT");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|a
+[3,4)|[2000-01-01,2010-01-01)|a
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_unique DEFAULT');
+
+# cleanup
+
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_no_key");
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION pub1");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_no_key");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION sub1");
+
+
+# #################################
+# Test with REPLICA IDENTITY FULL:
+# #################################
+
+# create tables on publisher
+
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE temporal_no_key (id int4range, valid_at daterange, a text)"
+);
+$node_publisher->safe_psql('postgres',
+	"ALTER TABLE temporal_no_key REPLICA IDENTITY FULL");
+
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text, PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_publisher->safe_psql('postgres',
+	"ALTER TABLE temporal_pk REPLICA IDENTITY FULL");
+
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE temporal_unique (id int4range NOT NULL, valid_at daterange NOT NULL, a text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_publisher->safe_psql('postgres',
+	"ALTER TABLE temporal_unique REPLICA IDENTITY FULL");
+
+# create tables on subscriber
+
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE temporal_no_key (id int4range, valid_at daterange, a text)"
+);
+$node_subscriber->safe_psql('postgres',
+	"ALTER TABLE temporal_no_key REPLICA IDENTITY FULL");
+
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text, PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_subscriber->safe_psql('postgres',
+	"ALTER TABLE temporal_pk REPLICA IDENTITY FULL");
+
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE temporal_unique (id int4range NOT NULL, valid_at daterange NOT NULL, a text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_subscriber->safe_psql('postgres',
+	"ALTER TABLE temporal_unique REPLICA IDENTITY FULL");
+
+# sync initial data:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_no_key (id, valid_at, a)
+   VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_pk (id, valid_at, a)
+   VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_unique (id, valid_at, a)
+   VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION pub1 FOR ALL TABLES");
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub1"
+);
+$node_subscriber->wait_for_subscription_sync;
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_no_key ORDER BY id, valid_at");
+is( $result,
+	qq{[1,2)|[2000-01-01,2010-01-01)|a},
+	'synced temporal_no_key FULL');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is($result, qq{[1,2)|[2000-01-01,2010-01-01)|a}, 'synced temporal_pk FULL');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result,
+	qq{[1,2)|[2000-01-01,2010-01-01)|a},
+	'synced temporal_unique FULL');
+
+# replicate with no key:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_no_key (id, valid_at, a)
+   VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+	"UPDATE temporal_no_key SET a = 'b' WHERE id = '[2,3)'");
+
+$node_publisher->safe_psql('postgres',
+	"DELETE FROM temporal_no_key WHERE id = '[3,4)'");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_no_key ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|b
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_no_key FULL');
+
+# replicate with a primary key:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_pk (id, valid_at, a)
+   VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+	"UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'");
+
+$node_publisher->safe_psql('postgres',
+	"DELETE FROM temporal_pk WHERE id = '[3,4)'");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|b
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk FULL');
+
+# replicate with a unique key:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_unique (id, valid_at, a)
+   VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+	"UPDATE temporal_unique SET a = 'b' WHERE id = '[2,3)'");
+
+$node_publisher->safe_psql('postgres',
+	"DELETE FROM temporal_unique WHERE id = '[3,4)'");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|b
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_unique FULL');
+
+# cleanup
+
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_no_key");
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION pub1");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_no_key");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION sub1");
+
+
+# #################################
+# Test with REPLICA IDENTITY USING INDEX
+# #################################
+
+# create tables on publisher
+
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text, PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_publisher->safe_psql('postgres',
+	"ALTER TABLE temporal_pk REPLICA IDENTITY USING INDEX temporal_pk_pkey");
+
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE temporal_unique (id int4range NOT NULL, valid_at daterange NOT NULL, a text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_publisher->safe_psql('postgres',
+	"ALTER TABLE temporal_unique REPLICA IDENTITY USING INDEX temporal_unique_id_valid_at_key"
+);
+
+# create tables on subscriber
+
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text, PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_subscriber->safe_psql('postgres',
+	"ALTER TABLE temporal_pk REPLICA IDENTITY USING INDEX temporal_pk_pkey");
+
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE temporal_unique (id int4range NOT NULL, valid_at daterange NOT NULL, a text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_subscriber->safe_psql('postgres',
+	"ALTER TABLE temporal_unique REPLICA IDENTITY USING INDEX temporal_unique_id_valid_at_key"
+);
+
+# sync initial data:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_pk (id, valid_at, a)
+   VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_unique (id, valid_at, a)
+   VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION pub1 FOR ALL TABLES");
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub1"
+);
+$node_subscriber->wait_for_subscription_sync;
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is( $result,
+	qq{[1,2)|[2000-01-01,2010-01-01)|a},
+	'synced temporal_pk USING INDEX');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result,
+	qq{[1,2)|[2000-01-01,2010-01-01)|a},
+	'synced temporal_unique USING INDEX');
+
+# replicate with a primary key:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_pk (id, valid_at, a)
+   VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+	"UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'");
+
+$node_publisher->safe_psql('postgres',
+	"DELETE FROM temporal_pk WHERE id = '[3,4)'");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|b
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk USING INDEX');
+
+# replicate with a unique key:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_unique (id, valid_at, a)
+   VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+	"UPDATE temporal_unique SET a = 'b' WHERE id = '[2,3)'");
+
+$node_publisher->safe_psql('postgres',
+	"DELETE FROM temporal_unique WHERE id = '[3,4)'");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|b
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_unique USING INDEX');
+
+# cleanup
+
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION pub1");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION sub1");
+
+
+# #################################
+# Test with REPLICA IDENTITY NOTHING
+# #################################
+
+# create tables on publisher
+
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE temporal_no_key (id int4range, valid_at daterange, a text, PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_publisher->safe_psql('postgres',
+	"ALTER TABLE temporal_no_key REPLICA IDENTITY NOTHING");
+
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text, PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_publisher->safe_psql('postgres',
+	"ALTER TABLE temporal_pk REPLICA IDENTITY NOTHING");
+
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE temporal_unique (id int4range, valid_at daterange, a text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_publisher->safe_psql('postgres',
+	"ALTER TABLE temporal_unique REPLICA IDENTITY NOTHING");
+
+# create tables on subscriber
+
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE temporal_no_key (id int4range, valid_at daterange, a text)"
+);
+$node_subscriber->safe_psql('postgres',
+	"ALTER TABLE temporal_no_key REPLICA IDENTITY NOTHING");
+
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE temporal_pk (id int4range, valid_at daterange, a text, PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_subscriber->safe_psql('postgres',
+	"ALTER TABLE temporal_pk REPLICA IDENTITY NOTHING");
+
+$node_subscriber->safe_psql('postgres',
+	"CREATE TABLE temporal_unique (id int4range, valid_at daterange, a text, UNIQUE (id, valid_at WITHOUT OVERLAPS))"
+);
+$node_subscriber->safe_psql('postgres',
+	"ALTER TABLE temporal_unique REPLICA IDENTITY NOTHING");
+
+# sync initial data:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_no_key (id, valid_at, a)
+   VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_pk (id, valid_at, a)
+   VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_unique (id, valid_at, a)
+   VALUES ('[1,2)', '[2000-01-01,2010-01-01)', 'a')");
+
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION pub1 FOR ALL TABLES");
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub1"
+);
+$node_subscriber->wait_for_subscription_sync;
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_no_key ORDER BY id, valid_at");
+is( $result,
+	qq{[1,2)|[2000-01-01,2010-01-01)|a},
+	'synced temporal_no_key NOTHING');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is($result, qq{[1,2)|[2000-01-01,2010-01-01)|a},
+	'synced temporal_pk NOTHING');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result,
+	qq{[1,2)|[2000-01-01,2010-01-01)|a},
+	'synced temporal_unique NOTHING');
+
+# replicate with no key:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_no_key (id, valid_at, a)
+   VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+	"UPDATE temporal_no_key SET a = 'b' WHERE id = '[2,3)'");
+is( $stderr,
+	qq(psql:<stdin>:1: ERROR:  cannot update table "temporal_no_key" because it does not have a replica identity and publishes updates
+HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
+	"can't UPDATE temporal_no_key NOTHING");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+	"DELETE FROM temporal_no_key WHERE id = '[3,4)'");
+is( $stderr,
+	qq(psql:<stdin>:1: ERROR:  cannot delete from table "temporal_no_key" because it does not have a replica identity and publishes deletes
+HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
+	"can't DELETE temporal_no_key NOTHING");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_no_key ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|a
+[3,4)|[2000-01-01,2010-01-01)|a
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_no_key NOTHING');
+
+# replicate with a primary key:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_pk (id, valid_at, a)
+   VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+	"UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'");
+is( $stderr,
+	qq(psql:<stdin>:1: ERROR:  cannot update table "temporal_pk" because it does not have a replica identity and publishes updates
+HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
+	"can't UPDATE temporal_pk NOTHING");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+	"DELETE FROM temporal_pk WHERE id = '[3,4)'");
+is( $stderr,
+	qq(psql:<stdin>:1: ERROR:  cannot delete from table "temporal_pk" because it does not have a replica identity and publishes deletes
+HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
+	"can't DELETE temporal_pk NOTHING");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_pk ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|a
+[3,4)|[2000-01-01,2010-01-01)|a
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk NOTHING');
+
+# replicate with a unique key:
+
+$node_publisher->safe_psql(
+	'postgres',
+	"INSERT INTO temporal_unique (id, valid_at, a)
+   VALUES ('[2,3)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[3,4)', '[2000-01-01,2010-01-01)', 'a'),
+          ('[4,5)', '[2000-01-01,2010-01-01)', 'a')");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+	"UPDATE temporal_unique SET a = 'b' WHERE id = '[2,3)'");
+is( $stderr,
+	qq(psql:<stdin>:1: ERROR:  cannot update table "temporal_unique" because it does not have a replica identity and publishes updates
+HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.),
+	"can't UPDATE temporal_unique NOTHING");
+
+($result, $stdout, $stderr) = $node_publisher->psql('postgres',
+	"DELETE FROM temporal_unique WHERE id = '[3,4)'");
+is( $stderr,
+	qq(psql:<stdin>:1: ERROR:  cannot delete from table "temporal_unique" because it does not have a replica identity and publishes deletes
+HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.),
+	"can't DELETE temporal_unique NOTHING");
+
+$node_publisher->wait_for_catchup('sub1');
+
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT * FROM temporal_unique ORDER BY id, valid_at");
+is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a
+[2,3)|[2000-01-01,2010-01-01)|a
+[3,4)|[2000-01-01,2010-01-01)|a
+[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_unique NOTHING');
+
+# cleanup
+
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_publisher->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION pub1");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_pk");
+$node_subscriber->safe_psql('postgres', "DROP TABLE temporal_unique");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION sub1");
+
+
+done_testing();
+
-- 
2.42.0



v44-0002-Add-without_portion-GiST-support-proc.patch (text/x-patch, 41.0K) download
From 51698b7a4e25d827b488038ca697ce3c22ba8ac9 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Sat, 30 Dec 2023 23:10:59 -0800
Subject: [PATCH v44 2/4] Add without_portion GiST support proc

This new support proc is used by RESTRICT foreign keys to compute the
portion of history that was lost when the application-time bounds of a
record change. This commit defines implementations for ranges and
multiranges.

The procs return SETOF their input type and work like minus but don't
fail on splits. The results never contain empty elements.

When we implement FOR PORTION OF, we will use these procs simiarly: to
compute leftovers that weren't touched by the UPDATE/DELETE.

Author: Paul Jungwirth
---
 contrib/bloom/blvalidate.c                    |   2 +-
 doc/src/sgml/gist.sgml                        | 107 +++++++++++-
 doc/src/sgml/xindex.sgml                      |   8 +-
 src/backend/access/brin/brin_validate.c       |   8 +-
 src/backend/access/gin/ginvalidate.c          |  12 +-
 src/backend/access/gist/gistvalidate.c        |  25 +--
 src/backend/access/hash/hashvalidate.c        |   4 +-
 src/backend/access/index/amvalidate.c         |   9 +-
 src/backend/access/nbtree/nbtvalidate.c       |   8 +-
 src/backend/access/spgist/spgvalidate.c       |   8 +-
 src/backend/utils/adt/multirangetypes.c       |  71 ++++++++
 src/backend/utils/adt/rangetypes.c            | 163 ++++++++++++++++++
 src/include/access/amvalidate.h               |   4 +-
 src/include/access/gist.h                     |   3 +-
 src/include/catalog/pg_amproc.dat             |   6 +
 src/include/catalog/pg_proc.dat               |   8 +
 src/include/utils/rangetypes.h                |   2 +
 src/test/regress/expected/multirangetypes.out | 116 +++++++++++++
 src/test/regress/expected/rangetypes.out      |  54 ++++++
 src/test/regress/sql/multirangetypes.sql      |  22 +++
 src/test/regress/sql/rangetypes.sql           |  10 ++
 21 files changed, 610 insertions(+), 40 deletions(-)

diff --git a/contrib/bloom/blvalidate.c b/contrib/bloom/blvalidate.c
index 20d3ab9109b..4b2e0ca90a5 100644
--- a/contrib/bloom/blvalidate.c
+++ b/contrib/bloom/blvalidate.c
@@ -103,7 +103,7 @@ blvalidate(Oid opclassoid)
 		switch (procform->amprocnum)
 		{
 			case BLOOM_HASH_PROC:
-				ok = check_amproc_signature(procform->amproc, INT4OID, false,
+				ok = check_amproc_signature(procform->amproc, INT4OID, false, false,
 											1, 1, opckeytype);
 				break;
 			case BLOOM_OPTIONS_PROC:
diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml
index 638d912dc2d..1fbddf1bfb0 100644
--- a/doc/src/sgml/gist.sgml
+++ b/doc/src/sgml/gist.sgml
@@ -266,7 +266,7 @@ CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops);
 
  <para>
    There are five methods that an index operator class for
-   <acronym>GiST</acronym> must provide, and seven that are optional.
+   <acronym>GiST</acronym> must provide, and eight that are optional.
    Correctness of the index is ensured
    by proper implementation of the <function>same</function>, <function>consistent</function>
    and <function>union</function> methods, while efficiency (size and speed) of the
@@ -294,6 +294,9 @@ CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops);
    <filename>src/include/access/stratnum.h</filename>) into strategy numbers
    used by the operator class.  This lets the core code look up operators for
    temporal constraint indexes.
+   The optional thirteenth method <function>without_portion</function> is used by
+   <literal>RESTRICT</literal> foreign keys to compute the portion of history
+   that was lost.
  </para>
 
  <variablelist>
@@ -1235,6 +1238,108 @@ my_stratnum(PG_FUNCTION_ARGS)
       </para>
      </listitem>
     </varlistentry>
+    <varlistentry>
+     <term><function>without_portion</function></term>
+     <listitem>
+      <para>
+       Given two values of this opclass, it subtracts the second for the first
+       and returns an array of the results.
+      </para>
+      <para>
+       This is used by temporal foreign keys to compute the part
+       of history that was lost by an update.
+      </para>
+
+      <para>
+       The <acronym>SQL</acronym> declaration of the function must look like
+       this (using <literal>my_range_without_portion</literal> as an example):
+
+<programlisting>
+CREATE OR REPLACE FUNCTION my_range_without_portion(anyrange, anyrange)
+RETURNS anyarray
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT;
+</programlisting>
+      </para>
+
+       <para>
+        The matching code in the C module could then follow this example:
+
+<programlisting>
+Datum
+my_range_without_portion(PG_FUNCTION_ARGS)
+{
+    typedef struct {
+        RangeType  *rs[2];
+        int         n;
+    } range_without_portion_fctx;
+
+    FuncCallContext *funcctx;
+    range_without_portion_fctx *fctx;
+    MemoryContext oldcontext;
+
+    /* stuff done only on the first call of the function */
+    if (SRF_IS_FIRSTCALL())
+    {
+        RangeType       *r1;
+        RangeType       *r2;
+        Oid              rngtypid;
+        TypeCacheEntry  *typcache;
+
+        /* create a function context for cross-call persistence */
+        funcctx = SRF_FIRSTCALL_INIT();
+
+        /*
+         * switch to memory context appropriate for multiple function calls
+         */
+        oldcontext = MemoryContextSwitchTo(funcctx-&gt;multi_call_memory_ctx);
+
+        r1 = PG_GETARG_RANGE_P(0);
+        r2 = PG_GETARG_RANGE_P(1);
+
+        /* Different types should be prevented by ANYRANGE matching rules */
+        if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2))
+            elog(ERROR, "range types do not match");
+
+        /* allocate memory for user context */
+        fctx = (range_without_portion_fctx *) palloc(sizeof(range_without_portion_fctx));
+
+        /*
+         * Initialize state.
+         * We can't store the range typcache in fn_extra because the caller
+         * uses that for the SRF state.
+         */
+        rngtypid = RangeTypeGetOid(r1);
+        typcache = lookup_type_cache(rngtypid, TYPECACHE_RANGE_INFO);
+        if (typcache-&gt;rngelemtype == NULL)
+            elog(ERROR, "type %u is not a range type", rngtypid);
+        range_without_portion_internal(typcache, r1, r2, fctx-&gt;rs, &amp;fctx-&gt;n);
+
+        funcctx-&gt;user_fctx = fctx;
+        MemoryContextSwitchTo(oldcontext);
+    }
+
+    /* stuff done on every call of the function */
+    funcctx = SRF_PERCALL_SETUP();
+    fctx = funcctx-&gt;user_fctx;
+
+    if (funcctx-&gt;call_cntr &lt; fctx-&gt;n)
+    {
+        /*
+         * We must keep these on separate lines
+         * because SRF_RETURN_NEXT does call_cntr++:
+         */
+        RangeType *ret = fctx-&gt;rs[funcctx-&gt;call_cntr];
+        SRF_RETURN_NEXT(funcctx, RangeTypePGetDatum(ret));
+    }
+    else
+        /* do when there is no more left */
+        SRF_RETURN_DONE(funcctx);
+}
+</programlisting>
+      </para>
+     </listitem>
+    </varlistentry>
   </variablelist>
 
   <para>
diff --git a/doc/src/sgml/xindex.sgml b/doc/src/sgml/xindex.sgml
index 3a19dab15e0..85ef539d07c 100644
--- a/doc/src/sgml/xindex.sgml
+++ b/doc/src/sgml/xindex.sgml
@@ -508,7 +508,7 @@
    </table>
 
   <para>
-   GiST indexes have twelve support functions, seven of which are optional,
+   GiST indexes have thirteen support functions, eight of which are optional,
    as shown in <xref linkend="xindex-gist-support-table"/>.
    (For more information see <xref linkend="gist"/>.)
   </para>
@@ -596,6 +596,12 @@
         used by the operator class (optional)</entry>
        <entry>12</entry>
       </row>
+      <row>
+       <entry><function>without_portion</function></entry>
+       <entry>computes remaining duration(s) after deleting
+       second parameter from first (optional)</entry>
+       <entry>13</entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/access/brin/brin_validate.c b/src/backend/access/brin/brin_validate.c
index 539ac9cb064..082f96b847d 100644
--- a/src/backend/access/brin/brin_validate.c
+++ b/src/backend/access/brin/brin_validate.c
@@ -87,21 +87,21 @@ brinvalidate(Oid opclassoid)
 		switch (procform->amprocnum)
 		{
 			case BRIN_PROCNUM_OPCINFO:
-				ok = check_amproc_signature(procform->amproc, INTERNALOID, true,
+				ok = check_amproc_signature(procform->amproc, INTERNALOID, false, true,
 											1, 1, INTERNALOID);
 				break;
 			case BRIN_PROCNUM_ADDVALUE:
-				ok = check_amproc_signature(procform->amproc, BOOLOID, true,
+				ok = check_amproc_signature(procform->amproc, BOOLOID, false, true,
 											4, 4, INTERNALOID, INTERNALOID,
 											INTERNALOID, INTERNALOID);
 				break;
 			case BRIN_PROCNUM_CONSISTENT:
-				ok = check_amproc_signature(procform->amproc, BOOLOID, true,
+				ok = check_amproc_signature(procform->amproc, BOOLOID, false, true,
 											3, 4, INTERNALOID, INTERNALOID,
 											INTERNALOID, INT4OID);
 				break;
 			case BRIN_PROCNUM_UNION:
-				ok = check_amproc_signature(procform->amproc, BOOLOID, true,
+				ok = check_amproc_signature(procform->amproc, BOOLOID, false, true,
 											3, 3, INTERNALOID, INTERNALOID,
 											INTERNALOID);
 				break;
diff --git a/src/backend/access/gin/ginvalidate.c b/src/backend/access/gin/ginvalidate.c
index 74420a129aa..748b94a893f 100644
--- a/src/backend/access/gin/ginvalidate.c
+++ b/src/backend/access/gin/ginvalidate.c
@@ -104,37 +104,37 @@ ginvalidate(Oid opclassoid)
 		switch (procform->amprocnum)
 		{
 			case GIN_COMPARE_PROC:
-				ok = check_amproc_signature(procform->amproc, INT4OID, false,
+				ok = check_amproc_signature(procform->amproc, INT4OID, false, false,
 											2, 2, opckeytype, opckeytype);
 				break;
 			case GIN_EXTRACTVALUE_PROC:
 				/* Some opclasses omit nullFlags */
-				ok = check_amproc_signature(procform->amproc, INTERNALOID, false,
+				ok = check_amproc_signature(procform->amproc, INTERNALOID, false, false,
 											2, 3, opcintype, INTERNALOID,
 											INTERNALOID);
 				break;
 			case GIN_EXTRACTQUERY_PROC:
 				/* Some opclasses omit nullFlags and searchMode */
-				ok = check_amproc_signature(procform->amproc, INTERNALOID, false,
+				ok = check_amproc_signature(procform->amproc, INTERNALOID, false, false,
 											5, 7, opcintype, INTERNALOID,
 											INT2OID, INTERNALOID, INTERNALOID,
 											INTERNALOID, INTERNALOID);
 				break;
 			case GIN_CONSISTENT_PROC:
 				/* Some opclasses omit queryKeys and nullFlags */
-				ok = check_amproc_signature(procform->amproc, BOOLOID, false,
+				ok = check_amproc_signature(procform->amproc, BOOLOID, false, false,
 											6, 8, INTERNALOID, INT2OID,
 											opcintype, INT4OID,
 											INTERNALOID, INTERNALOID,
 											INTERNALOID, INTERNALOID);
 				break;
 			case GIN_COMPARE_PARTIAL_PROC:
-				ok = check_amproc_signature(procform->amproc, INT4OID, false,
+				ok = check_amproc_signature(procform->amproc, INT4OID, false, false,
 											4, 4, opckeytype, opckeytype,
 											INT2OID, INTERNALOID);
 				break;
 			case GIN_TRICONSISTENT_PROC:
-				ok = check_amproc_signature(procform->amproc, CHAROID, false,
+				ok = check_amproc_signature(procform->amproc, CHAROID, false, false,
 											7, 7, INTERNALOID, INT2OID,
 											opcintype, INT4OID,
 											INTERNALOID, INTERNALOID,
diff --git a/src/backend/access/gist/gistvalidate.c b/src/backend/access/gist/gistvalidate.c
index 0901543a60a..38740b46bbf 100644
--- a/src/backend/access/gist/gistvalidate.c
+++ b/src/backend/access/gist/gistvalidate.c
@@ -106,36 +106,36 @@ gistvalidate(Oid opclassoid)
 		switch (procform->amprocnum)
 		{
 			case GIST_CONSISTENT_PROC:
-				ok = check_amproc_signature(procform->amproc, BOOLOID, false,
+				ok = check_amproc_signature(procform->amproc, BOOLOID, false, false,
 											5, 5, INTERNALOID, opcintype,
 											INT2OID, OIDOID, INTERNALOID);
 				break;
 			case GIST_UNION_PROC:
-				ok = check_amproc_signature(procform->amproc, opckeytype, false,
+				ok = check_amproc_signature(procform->amproc, opckeytype, false, false,
 											2, 2, INTERNALOID, INTERNALOID);
 				break;
 			case GIST_COMPRESS_PROC:
 			case GIST_DECOMPRESS_PROC:
 			case GIST_FETCH_PROC:
-				ok = check_amproc_signature(procform->amproc, INTERNALOID, true,
+				ok = check_amproc_signature(procform->amproc, INTERNALOID, false, true,
 											1, 1, INTERNALOID);
 				break;
 			case GIST_PENALTY_PROC:
-				ok = check_amproc_signature(procform->amproc, INTERNALOID, true,
+				ok = check_amproc_signature(procform->amproc, INTERNALOID, false, true,
 											3, 3, INTERNALOID,
 											INTERNALOID, INTERNALOID);
 				break;
 			case GIST_PICKSPLIT_PROC:
-				ok = check_amproc_signature(procform->amproc, INTERNALOID, true,
+				ok = check_amproc_signature(procform->amproc, INTERNALOID, false, true,
 											2, 2, INTERNALOID, INTERNALOID);
 				break;
 			case GIST_EQUAL_PROC:
-				ok = check_amproc_signature(procform->amproc, INTERNALOID, false,
+				ok = check_amproc_signature(procform->amproc, INTERNALOID, false, false,
 											3, 3, opckeytype, opckeytype,
 											INTERNALOID);
 				break;
 			case GIST_DISTANCE_PROC:
-				ok = check_amproc_signature(procform->amproc, FLOAT8OID, false,
+				ok = check_amproc_signature(procform->amproc, FLOAT8OID, false, false,
 											5, 5, INTERNALOID, opcintype,
 											INT2OID, OIDOID, INTERNALOID);
 				break;
@@ -143,13 +143,17 @@ gistvalidate(Oid opclassoid)
 				ok = check_amoptsproc_signature(procform->amproc);
 				break;
 			case GIST_SORTSUPPORT_PROC:
-				ok = check_amproc_signature(procform->amproc, VOIDOID, true,
+				ok = check_amproc_signature(procform->amproc, VOIDOID, false, true,
 											1, 1, INTERNALOID);
 				break;
 			case GIST_STRATNUM_PROC:
-				ok = check_amproc_signature(procform->amproc, INT2OID, true,
+				ok = check_amproc_signature(procform->amproc, INT2OID, false, true,
 											1, 1, INT2OID);
 				break;
+			case GIST_WITHOUT_PORTION_PROC:
+				ok = check_amproc_signature(procform->amproc, opcintype, true, true,
+											2, 2, opcintype, opcintype);
+				break;
 			default:
 				ereport(INFO,
 						(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
@@ -271,7 +275,7 @@ gistvalidate(Oid opclassoid)
 		if (i == GIST_DISTANCE_PROC || i == GIST_FETCH_PROC ||
 			i == GIST_COMPRESS_PROC || i == GIST_DECOMPRESS_PROC ||
 			i == GIST_OPTIONS_PROC || i == GIST_SORTSUPPORT_PROC ||
-			i == GIST_STRATNUM_PROC)
+			i == GIST_STRATNUM_PROC || i == GIST_WITHOUT_PORTION_PROC)
 			continue;			/* optional methods */
 		ereport(INFO,
 				(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
@@ -344,6 +348,7 @@ gistadjustmembers(Oid opfamilyoid,
 			case GIST_OPTIONS_PROC:
 			case GIST_SORTSUPPORT_PROC:
 			case GIST_STRATNUM_PROC:
+			case GIST_WITHOUT_PORTION_PROC:
 				/* Optional, so force it to be a soft family dependency */
 				op->ref_is_hard = false;
 				op->ref_is_family = true;
diff --git a/src/backend/access/hash/hashvalidate.c b/src/backend/access/hash/hashvalidate.c
index 66a3b7b49ad..3a8bd635b38 100644
--- a/src/backend/access/hash/hashvalidate.c
+++ b/src/backend/access/hash/hashvalidate.c
@@ -104,11 +104,11 @@ hashvalidate(Oid opclassoid)
 		switch (procform->amprocnum)
 		{
 			case HASHSTANDARD_PROC:
-				ok = check_amproc_signature(procform->amproc, INT4OID, true,
+				ok = check_amproc_signature(procform->amproc, INT4OID, false, true,
 											1, 1, procform->amproclefttype);
 				break;
 			case HASHEXTENDED_PROC:
-				ok = check_amproc_signature(procform->amproc, INT8OID, true,
+				ok = check_amproc_signature(procform->amproc, INT8OID, false, true,
 											2, 2, procform->amproclefttype, INT8OID);
 				break;
 			case HASHOPTIONS_PROC:
diff --git a/src/backend/access/index/amvalidate.c b/src/backend/access/index/amvalidate.c
index 32bb477f328..b10308b9b66 100644
--- a/src/backend/access/index/amvalidate.c
+++ b/src/backend/access/index/amvalidate.c
@@ -149,7 +149,7 @@ identify_opfamily_groups(CatCList *oprlist, CatCList *proclist)
  * In any case the function result type must match restype exactly.
  */
 bool
-check_amproc_signature(Oid funcid, Oid restype, bool exact,
+check_amproc_signature(Oid funcid, Oid restype, bool retset, bool exact,
 					   int minargs, int maxargs,...)
 {
 	bool		result = true;
@@ -163,8 +163,9 @@ check_amproc_signature(Oid funcid, Oid restype, bool exact,
 		elog(ERROR, "cache lookup failed for function %u", funcid);
 	procform = (Form_pg_proc) GETSTRUCT(tp);
 
-	if (procform->prorettype != restype || procform->proretset ||
-		procform->pronargs < minargs || procform->pronargs > maxargs)
+	if ((procform->prorettype != restype && OidIsValid(restype))
+		|| procform->proretset != retset || procform->pronargs < minargs
+		|| procform->pronargs > maxargs)
 		result = false;
 
 	va_start(ap, maxargs);
@@ -191,7 +192,7 @@ check_amproc_signature(Oid funcid, Oid restype, bool exact,
 bool
 check_amoptsproc_signature(Oid funcid)
 {
-	return check_amproc_signature(funcid, VOIDOID, true, 1, 1, INTERNALOID);
+	return check_amproc_signature(funcid, VOIDOID, false, true, 1, 1, INTERNALOID);
 }
 
 /*
diff --git a/src/backend/access/nbtree/nbtvalidate.c b/src/backend/access/nbtree/nbtvalidate.c
index e9d4cd60de3..04e43ef1dce 100644
--- a/src/backend/access/nbtree/nbtvalidate.c
+++ b/src/backend/access/nbtree/nbtvalidate.c
@@ -91,16 +91,16 @@ btvalidate(Oid opclassoid)
 		switch (procform->amprocnum)
 		{
 			case BTORDER_PROC:
-				ok = check_amproc_signature(procform->amproc, INT4OID, true,
+				ok = check_amproc_signature(procform->amproc, INT4OID, false, true,
 											2, 2, procform->amproclefttype,
 											procform->amprocrighttype);
 				break;
 			case BTSORTSUPPORT_PROC:
-				ok = check_amproc_signature(procform->amproc, VOIDOID, true,
+				ok = check_amproc_signature(procform->amproc, VOIDOID, false, true,
 											1, 1, INTERNALOID);
 				break;
 			case BTINRANGE_PROC:
-				ok = check_amproc_signature(procform->amproc, BOOLOID, true,
+				ok = check_amproc_signature(procform->amproc, BOOLOID, false, true,
 											5, 5,
 											procform->amproclefttype,
 											procform->amproclefttype,
@@ -108,7 +108,7 @@ btvalidate(Oid opclassoid)
 											BOOLOID, BOOLOID);
 				break;
 			case BTEQUALIMAGE_PROC:
-				ok = check_amproc_signature(procform->amproc, BOOLOID, true,
+				ok = check_amproc_signature(procform->amproc, BOOLOID, false, true,
 											1, 1, OIDOID);
 				break;
 			case BTOPTIONS_PROC:
diff --git a/src/backend/access/spgist/spgvalidate.c b/src/backend/access/spgist/spgvalidate.c
index 850c3b675d6..f7a397cb8cf 100644
--- a/src/backend/access/spgist/spgvalidate.c
+++ b/src/backend/access/spgist/spgvalidate.c
@@ -109,7 +109,7 @@ spgvalidate(Oid opclassoid)
 		switch (procform->amprocnum)
 		{
 			case SPGIST_CONFIG_PROC:
-				ok = check_amproc_signature(procform->amproc, VOIDOID, true,
+				ok = check_amproc_signature(procform->amproc, VOIDOID, false, true,
 											2, 2, INTERNALOID, INTERNALOID);
 				configIn.attType = procform->amproclefttype;
 				memset(&configOut, 0, sizeof(configOut));
@@ -164,11 +164,11 @@ spgvalidate(Oid opclassoid)
 			case SPGIST_CHOOSE_PROC:
 			case SPGIST_PICKSPLIT_PROC:
 			case SPGIST_INNER_CONSISTENT_PROC:
-				ok = check_amproc_signature(procform->amproc, VOIDOID, true,
+				ok = check_amproc_signature(procform->amproc, VOIDOID, false, true,
 											2, 2, INTERNALOID, INTERNALOID);
 				break;
 			case SPGIST_LEAF_CONSISTENT_PROC:
-				ok = check_amproc_signature(procform->amproc, BOOLOID, true,
+				ok = check_amproc_signature(procform->amproc, BOOLOID, false, true,
 											2, 2, INTERNALOID, INTERNALOID);
 				break;
 			case SPGIST_COMPRESS_PROC:
@@ -177,7 +177,7 @@ spgvalidate(Oid opclassoid)
 					ok = false;
 				else
 					ok = check_amproc_signature(procform->amproc,
-												configOutLeafType, true,
+												configOutLeafType, false, true,
 												1, 1, procform->amproclefttype);
 				break;
 			case SPGIST_OPTIONS_PROC:
diff --git a/src/backend/utils/adt/multirangetypes.c b/src/backend/utils/adt/multirangetypes.c
index 558c6c18c34..8f722b6722d 100644
--- a/src/backend/utils/adt/multirangetypes.c
+++ b/src/backend/utils/adt/multirangetypes.c
@@ -1225,6 +1225,77 @@ multirange_minus_internal(Oid mltrngtypoid, TypeCacheEntry *rangetyp,
 	return make_multirange(mltrngtypoid, rangetyp, range_count3, ranges3);
 }
 
+/*
+ * multirange minus but returning the result as a SRF,
+ * with no rows if the result would be empty.
+ */
+Datum
+multirange_without_portion(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+	MemoryContext oldcontext;
+
+	if (!SRF_IS_FIRSTCALL())
+	{
+		/* We never have more than one result */
+		funcctx = SRF_PERCALL_SETUP();
+		SRF_RETURN_DONE(funcctx);
+	}
+	else
+	{
+		MultirangeType *mr1;
+		MultirangeType *mr2;
+		Oid			mltrngtypoid;
+		TypeCacheEntry *typcache;
+		TypeCacheEntry *rangetyp;
+		int32		range_count1;
+		int32		range_count2;
+		RangeType **ranges1;
+		RangeType **ranges2;
+		MultirangeType *mr;
+
+		funcctx = SRF_FIRSTCALL_INIT();
+
+		/*
+		 * switch to memory context appropriate for multiple function calls
+		 */
+		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+		/* get args, detoasting into multi-call memory context */
+		mr1 = PG_GETARG_MULTIRANGE_P(0);
+		mr2 = PG_GETARG_MULTIRANGE_P(1);
+
+		mltrngtypoid = MultirangeTypeGetOid(mr1);
+		typcache = lookup_type_cache(mltrngtypoid, TYPECACHE_MULTIRANGE_INFO);
+		if (typcache->rngtype == NULL)
+			elog(ERROR, "type %u is not a multirange type", mltrngtypoid);
+		rangetyp = typcache->rngtype;
+
+		if (MultirangeIsEmpty(mr1) || MultirangeIsEmpty(mr2))
+			mr = mr1;
+		else
+		{
+			multirange_deserialize(rangetyp, mr1, &range_count1, &ranges1);
+			multirange_deserialize(rangetyp, mr2, &range_count2, &ranges2);
+
+			mr = multirange_minus_internal(mltrngtypoid,
+										   rangetyp,
+										   range_count1,
+										   ranges1,
+										   range_count2,
+										   ranges2);
+		}
+
+		MemoryContextSwitchTo(oldcontext);
+
+		funcctx = SRF_PERCALL_SETUP();
+		if (MultirangeIsEmpty(mr))
+			SRF_RETURN_DONE(funcctx);
+		else
+			SRF_RETURN_NEXT(funcctx, MultirangeTypePGetDatum(mr));
+	}
+}
+
 /* multirange intersection */
 Datum
 multirange_intersect(PG_FUNCTION_ARGS)
diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index 2d94a6b8774..cd0ffe231e3 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -31,6 +31,7 @@
 #include "postgres.h"
 
 #include "common/hashfn.h"
+#include "funcapi.h"
 #include "libpq/pqformat.h"
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
@@ -39,6 +40,7 @@
 #include "optimizer/clauses.h"
 #include "optimizer/cost.h"
 #include "optimizer/optimizer.h"
+#include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/date.h"
 #include "utils/lsyscache.h"
@@ -1213,6 +1215,167 @@ range_split_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeT
 	return false;
 }
 
+/* subtraction but returning an array to accommodate splits */
+Datum
+range_without_portion(PG_FUNCTION_ARGS)
+{
+	typedef struct {
+		RangeType  *rs[2];
+		int			n;
+	} range_without_portion_fctx;
+
+	FuncCallContext *funcctx;
+	range_without_portion_fctx *fctx;
+	MemoryContext oldcontext;
+
+	/* stuff done only on the first call of the function */
+	if (SRF_IS_FIRSTCALL())
+	{
+		RangeType	   *r1;
+		RangeType	   *r2;
+		Oid				rngtypid;
+		TypeCacheEntry *typcache;
+
+		/* create a function context for cross-call persistence */
+		funcctx = SRF_FIRSTCALL_INIT();
+
+		/*
+		 * switch to memory context appropriate for multiple function calls
+		 */
+		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+		r1 = PG_GETARG_RANGE_P(0);
+		r2 = PG_GETARG_RANGE_P(1);
+
+		/* Different types should be prevented by ANYRANGE matching rules */
+		if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2))
+			elog(ERROR, "range types do not match");
+
+		/* allocate memory for user context */
+		fctx = (range_without_portion_fctx *) palloc(sizeof(range_without_portion_fctx));
+
+		/*
+		 * Initialize state.
+		 * We can't store the range typcache in fn_extra because the caller
+		 * uses that for the SRF state.
+		 */
+		rngtypid = RangeTypeGetOid(r1);
+		typcache = lookup_type_cache(rngtypid, TYPECACHE_RANGE_INFO);
+		if (typcache->rngelemtype == NULL)
+			elog(ERROR, "type %u is not a range type", rngtypid);
+		range_without_portion_internal(typcache, r1, r2, fctx->rs, &fctx->n);
+
+		funcctx->user_fctx = fctx;
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	/* stuff done on every call of the function */
+	funcctx = SRF_PERCALL_SETUP();
+	fctx = funcctx->user_fctx;
+
+	if (funcctx->call_cntr < fctx->n)
+	{
+		/*
+		 * We must keep these on separate lines
+		 * because SRF_RETURN_NEXT does call_cntr++:
+		 */
+		RangeType *ret = fctx->rs[funcctx->call_cntr];
+		SRF_RETURN_NEXT(funcctx, RangeTypePGetDatum(ret));
+	}
+	else
+		/* do when there is no more left */
+		SRF_RETURN_DONE(funcctx);
+}
+
+/*
+ * range_without_portion_internal - Sets outputs and outputn to the ranges
+ * remaining and their count (respectively) after subtracting r2 from r1.
+ * The array should never contain empty ranges.
+ * The outputs will be ordered. We expect that outputs is an array of
+ * RangeType pointers, already allocated with two elements.
+ */
+void
+range_without_portion_internal(TypeCacheEntry *typcache, RangeType *r1,
+							   RangeType *r2, RangeType **outputs, int *outputn)
+{
+	int			cmp_l1l2,
+				cmp_l1u2,
+				cmp_u1l2,
+				cmp_u1u2;
+	RangeBound	lower1,
+				lower2;
+	RangeBound	upper1,
+				upper2;
+	bool		empty1,
+				empty2;
+
+	range_deserialize(typcache, r1, &lower1, &upper1, &empty1);
+	range_deserialize(typcache, r2, &lower2, &upper2, &empty2);
+
+	if (empty1)
+	{
+		/* if r1 is empty then r1 - r2 is empty, so return zero results */
+		*outputn = 0;
+		return;
+	}
+	else if (empty2)
+	{
+		/* r2 is empty so the result is just r1 (which we know is not empty) */
+		outputs[0] = r1;
+		*outputn = 1;
+		return;
+	}
+
+	/*
+	 * Use the same logic as range_minus_internal,
+	 * but support the split case
+	 */
+	cmp_l1l2 = range_cmp_bounds(typcache, &lower1, &lower2);
+	cmp_l1u2 = range_cmp_bounds(typcache, &lower1, &upper2);
+	cmp_u1l2 = range_cmp_bounds(typcache, &upper1, &lower2);
+	cmp_u1u2 = range_cmp_bounds(typcache, &upper1, &upper2);
+
+	if (cmp_l1l2 < 0 && cmp_u1u2 > 0)
+	{
+		lower2.inclusive = !lower2.inclusive;
+		lower2.lower = false;	/* it will become the upper bound */
+		outputs[0] = make_range(typcache, &lower1, &lower2, false, NULL);
+
+		upper2.inclusive = !upper2.inclusive;
+		upper2.lower = true;	/* it will become the lower bound */
+		outputs[1] = make_range(typcache, &upper2, &upper1, false, NULL);
+
+		*outputn = 2;
+	}
+	else if (cmp_l1u2 > 0 || cmp_u1l2 < 0)
+	{
+		outputs[0] = r1;
+		*outputn = 1;
+	}
+	else if (cmp_l1l2 >= 0 && cmp_u1u2 <= 0)
+	{
+		*outputn = 0;
+	}
+	else if (cmp_l1l2 <= 0 && cmp_u1l2 >= 0 && cmp_u1u2 <= 0)
+	{
+		lower2.inclusive = !lower2.inclusive;
+		lower2.lower = false;	/* it will become the upper bound */
+		outputs[0] = make_range(typcache, &lower1, &lower2, false, NULL);
+		*outputn = 1;
+	}
+	else if (cmp_l1l2 >= 0 && cmp_u1u2 >= 0 && cmp_l1u2 <= 0)
+	{
+		upper2.inclusive = !upper2.inclusive;
+		upper2.lower = true;	/* it will become the lower bound */
+		outputs[0] = make_range(typcache, &upper2, &upper1, false, NULL);
+		*outputn = 1;
+	}
+	else
+	{
+		elog(ERROR, "unexpected case in range_without_portion");
+	}
+}
+
 /* range -> range aggregate functions */
 
 Datum
diff --git a/src/include/access/amvalidate.h b/src/include/access/amvalidate.h
index 424ab63fa5a..61039542dac 100644
--- a/src/include/access/amvalidate.h
+++ b/src/include/access/amvalidate.h
@@ -28,8 +28,8 @@ typedef struct OpFamilyOpFuncGroup
 
 /* Functions in access/index/amvalidate.c */
 extern List *identify_opfamily_groups(CatCList *oprlist, CatCList *proclist);
-extern bool check_amproc_signature(Oid funcid, Oid restype, bool exact,
-								   int minargs, int maxargs,...);
+extern bool check_amproc_signature(Oid funcid, Oid restype, bool retset,
+								   bool exact, int minargs, int maxargs,...);
 extern bool check_amoptsproc_signature(Oid funcid);
 extern bool check_amop_signature(Oid opno, Oid restype,
 								 Oid lefttype, Oid righttype);
diff --git a/src/include/access/gist.h b/src/include/access/gist.h
index 22dd04c1418..8d6c99f5ebe 100644
--- a/src/include/access/gist.h
+++ b/src/include/access/gist.h
@@ -40,7 +40,8 @@
 #define GIST_OPTIONS_PROC				10
 #define GIST_SORTSUPPORT_PROC			11
 #define GIST_STRATNUM_PROC				12
-#define GISTNProcs					12
+#define GIST_WITHOUT_PORTION_PROC		13
+#define GISTNProcs					13
 
 /*
  * Page opaque data in a GiST index page.
diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index 5d7fe292bf6..0a4db325faa 100644
--- a/src/include/catalog/pg_amproc.dat
+++ b/src/include/catalog/pg_amproc.dat
@@ -609,6 +609,9 @@
 { amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange',
   amprocrighttype => 'anyrange', amprocnum => '12',
   amproc => 'gist_stratnum_identity' },
+{ amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange',
+  amprocrighttype => 'anyrange', amprocnum => '13',
+  amproc => 'range_without_portion(anyrange,anyrange)' },
 { amprocfamily => 'gist/network_ops', amproclefttype => 'inet',
   amprocrighttype => 'inet', amprocnum => '1',
   amproc => 'inet_gist_consistent' },
@@ -649,6 +652,9 @@
 { amprocfamily => 'gist/multirange_ops', amproclefttype => 'anymultirange',
   amprocrighttype => 'anymultirange', amprocnum => '12',
   amproc => 'gist_stratnum_identity' },
+{ amprocfamily => 'gist/multirange_ops', amproclefttype => 'anymultirange',
+  amprocrighttype => 'anymultirange', amprocnum => '13',
+  amproc => 'multirange_without_portion(anymultirange,anymultirange)' },
 
 # gin
 { amprocfamily => 'gin/array_ops', amproclefttype => 'anyarray',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index cbbe8acd382..4139df94c9b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10739,6 +10739,10 @@
 { oid => '3869',
   proname => 'range_minus', prorettype => 'anyrange',
   proargtypes => 'anyrange anyrange', prosrc => 'range_minus' },
+{ oid => '8408', descr => 'remove portion from range',
+  proname => 'range_without_portion', prorows => '2',
+  proretset => 't', prorettype => 'anyrange',
+  proargtypes => 'anyrange anyrange', prosrc => 'range_without_portion' },
 { oid => '3870', descr => 'less-equal-greater',
   proname => 'range_cmp', prorettype => 'int4',
   proargtypes => 'anyrange anyrange', prosrc => 'range_cmp' },
@@ -11026,6 +11030,10 @@
 { oid => '4271',
   proname => 'multirange_minus', prorettype => 'anymultirange',
   proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_minus' },
+{ oid => '8406', descr => 'remove portion from multirange',
+  proname => 'multirange_without_portion', prorows => '1',
+  proretset => 't', prorettype => 'anymultirange',
+  proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_without_portion' },
 { oid => '4272',
   proname => 'multirange_intersect', prorettype => 'anymultirange',
   proargtypes => 'anymultirange anymultirange',
diff --git a/src/include/utils/rangetypes.h b/src/include/utils/rangetypes.h
index 2b574873cef..e1b256b65f4 100644
--- a/src/include/utils/rangetypes.h
+++ b/src/include/utils/rangetypes.h
@@ -164,5 +164,7 @@ extern RangeType *make_empty_range(TypeCacheEntry *typcache);
 extern bool range_split_internal(TypeCacheEntry *typcache, const RangeType *r1,
 								 const RangeType *r2, RangeType **output1,
 								 RangeType **output2);
+extern void range_without_portion_internal(TypeCacheEntry *typcache, RangeType *r1,
+									RangeType *r2, RangeType **outputs, int *outputn);
 
 #endif							/* RANGETYPES_H */
diff --git a/src/test/regress/expected/multirangetypes.out b/src/test/regress/expected/multirangetypes.out
index c6363ebeb24..11aa282ff35 100644
--- a/src/test/regress/expected/multirangetypes.out
+++ b/src/test/regress/expected/multirangetypes.out
@@ -2200,6 +2200,122 @@ SELECT nummultirange(numrange(1,2), numrange(4,5)) - nummultirange(numrange(-2,0
  {[1,2),[4,5)}
 (1 row)
 
+-- without_portion
+SELECT multirange_without_portion(nummultirange(), nummultirange());
+ multirange_without_portion 
+----------------------------
+(0 rows)
+
+SELECT multirange_without_portion(nummultirange(), nummultirange(numrange(1,2)));
+ multirange_without_portion 
+----------------------------
+(0 rows)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange());
+ multirange_without_portion 
+----------------------------
+ {[1,2)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(3,4)), nummultirange());
+ multirange_without_portion 
+----------------------------
+ {[1,2),[3,4)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(1,2)));
+ multirange_without_portion 
+----------------------------
+(0 rows)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(2,4)));
+ multirange_without_portion 
+----------------------------
+ {[1,2)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(3,4)));
+ multirange_without_portion 
+----------------------------
+ {[1,2)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(1,2)));
+ multirange_without_portion 
+----------------------------
+ {[2,4)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(2,3)));
+ multirange_without_portion 
+----------------------------
+ {[1,2),[3,4)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(0,8)));
+ multirange_without_portion 
+----------------------------
+(0 rows)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(0,2)));
+ multirange_without_portion 
+----------------------------
+ {[2,4)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,8)), nummultirange(numrange(0,2), numrange(3,4)));
+ multirange_without_portion 
+----------------------------
+ {[2,3),[4,8)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,8)), nummultirange(numrange(2,3), numrange(5,null)));
+ multirange_without_portion 
+----------------------------
+ {[1,2),[3,5)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(-2,0)));
+ multirange_without_portion 
+----------------------------
+ {[1,2),[4,5)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(2,4)));
+ multirange_without_portion 
+----------------------------
+ {[1,2),[4,5)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(3,5)));
+ multirange_without_portion 
+----------------------------
+ {[1,2)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(0,9)));
+ multirange_without_portion 
+----------------------------
+(0 rows)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,3), numrange(4,5)), nummultirange(numrange(2,9)));
+ multirange_without_portion 
+----------------------------
+ {[1,2)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(8,9)));
+ multirange_without_portion 
+----------------------------
+ {[1,2),[4,5)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(-2,0), numrange(8,9)));
+ multirange_without_portion 
+----------------------------
+ {[1,2),[4,5)}
+(1 row)
+
 -- intersection
 SELECT nummultirange() * nummultirange();
  ?column? 
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index a7cc220bf0d..ab2309e8c1d 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -481,6 +481,60 @@ select range_minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]'));
  empty
 (1 row)
 
+select range_without_portion('empty'::numrange, numrange(2.0, 3.0));
+ range_without_portion 
+-----------------------
+(0 rows)
+
+select range_without_portion(numrange(1.1, 2.2), 'empty'::numrange);
+ range_without_portion 
+-----------------------
+ [1.1,2.2)
+(1 row)
+
+select range_without_portion(numrange(1.1, 2.2), numrange(2.0, 3.0));
+ range_without_portion 
+-----------------------
+ [1.1,2.0)
+(1 row)
+
+select range_without_portion(numrange(1.1, 2.2), numrange(2.2, 3.0));
+ range_without_portion 
+-----------------------
+ [1.1,2.2)
+(1 row)
+
+select range_without_portion(numrange(1.1, 2.2,'[]'), numrange(2.0, 3.0));
+ range_without_portion 
+-----------------------
+ [1.1,2.0)
+(1 row)
+
+select range_without_portion(numrange(1.0, 3.0), numrange(1.5, 2.0));
+ range_without_portion 
+-----------------------
+ [1.0,1.5)
+ [2.0,3.0)
+(2 rows)
+
+select range_without_portion(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]'));
+ range_without_portion 
+-----------------------
+ [10.1,12.2]
+(1 row)
+
+select range_without_portion(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]'));
+ range_without_portion 
+-----------------------
+(0 rows)
+
+select range_without_portion(numrange(1.0,3.0,'[]'), numrange(1.5,2.0,'(]'));
+ range_without_portion 
+-----------------------
+ [1.0,1.5]
+ (2.0,3.0]
+(2 rows)
+
 select numrange(4.5, 5.5, '[]') && numrange(5.5, 6.5);
  ?column? 
 ----------
diff --git a/src/test/regress/sql/multirangetypes.sql b/src/test/regress/sql/multirangetypes.sql
index 41d5524285a..0bfa71caca0 100644
--- a/src/test/regress/sql/multirangetypes.sql
+++ b/src/test/regress/sql/multirangetypes.sql
@@ -414,6 +414,28 @@ SELECT nummultirange(numrange(1,3), numrange(4,5)) - nummultirange(numrange(2,9)
 SELECT nummultirange(numrange(1,2), numrange(4,5)) - nummultirange(numrange(8,9));
 SELECT nummultirange(numrange(1,2), numrange(4,5)) - nummultirange(numrange(-2,0), numrange(8,9));
 
+-- without_portion
+SELECT multirange_without_portion(nummultirange(), nummultirange());
+SELECT multirange_without_portion(nummultirange(), nummultirange(numrange(1,2)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange());
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(3,4)), nummultirange());
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(1,2)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(2,4)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(3,4)));
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(1,2)));
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(2,3)));
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(0,8)));
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(0,2)));
+SELECT multirange_without_portion(nummultirange(numrange(1,8)), nummultirange(numrange(0,2), numrange(3,4)));
+SELECT multirange_without_portion(nummultirange(numrange(1,8)), nummultirange(numrange(2,3), numrange(5,null)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(-2,0)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(2,4)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(3,5)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(0,9)));
+SELECT multirange_without_portion(nummultirange(numrange(1,3), numrange(4,5)), nummultirange(numrange(2,9)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(8,9)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(-2,0), numrange(8,9)));
+
 -- intersection
 SELECT nummultirange() * nummultirange();
 SELECT nummultirange() * nummultirange(numrange(1,2));
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index a5ecdf5372f..7fc805d9ffa 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -107,6 +107,16 @@ select numrange(1.1, 2.2,'[]') - numrange(2.0, 3.0);
 select range_minus(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]'));
 select range_minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]'));
 
+select range_without_portion('empty'::numrange, numrange(2.0, 3.0));
+select range_without_portion(numrange(1.1, 2.2), 'empty'::numrange);
+select range_without_portion(numrange(1.1, 2.2), numrange(2.0, 3.0));
+select range_without_portion(numrange(1.1, 2.2), numrange(2.2, 3.0));
+select range_without_portion(numrange(1.1, 2.2,'[]'), numrange(2.0, 3.0));
+select range_without_portion(numrange(1.0, 3.0), numrange(1.5, 2.0));
+select range_without_portion(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]'));
+select range_without_portion(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]'));
+select range_without_portion(numrange(1.0,3.0,'[]'), numrange(1.5,2.0,'(]'));
+
 select numrange(4.5, 5.5, '[]') && numrange(5.5, 6.5);
 select numrange(1.0, 2.0) << numrange(3.0, 4.0);
 select numrange(1.0, 3.0,'[]') << numrange(3.0, 4.0,'[]');
-- 
2.42.0



v44-0003-Fix-NOACTION-temporal-foreign-keys-when-the-refe.patch (text/x-patch, 23.6K) download
From bb8fd8f4458f36e3ba218971321b4bd85652f873 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Sat, 9 Nov 2024 14:05:48 -0800
Subject: [PATCH v44 3/4] Fix NOACTION temporal foreign keys when the
 referenced endpoints change

If a referenced UPDATE changes the temporal start/end times, shrinking
the span the row is valid, we get a false return from ri_Check_Pk_Match,
but overlapping references may still be valid, if their reference didn't
overlap with the removed span.

We need to consider what span(s) are still provided in the referenced
table. Instead of returning that from ri_Check_Pk_Match, we can just
we look it up in the main SQL query.

Reported-by: Sam Gabrielsson
Author: Paul Jungwirth
---
 src/backend/catalog/pg_constraint.c           | 14 ++-
 src/backend/commands/tablecmds.c              |  4 +-
 src/backend/utils/adt/ri_triggers.c           | 99 ++++++++++++++++++-
 src/include/catalog/pg_constraint.h           |  3 +-
 src/include/catalog/pg_operator.dat           |  6 +-
 .../regress/expected/without_overlaps.out     | 68 ++++++++++++-
 src/test/regress/sql/without_overlaps.sql     | 60 ++++++++++-
 7 files changed, 243 insertions(+), 11 deletions(-)

diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 9c05a98d28c..c7fdbeedc0a 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -1613,7 +1613,8 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
 void
 FindFKPeriodOpers(Oid opclass,
 				  Oid *containedbyoperoid,
-				  Oid *aggedcontainedbyoperoid)
+				  Oid *aggedcontainedbyoperoid,
+				  Oid *intersectoperoid)
 {
 	Oid			opfamily = InvalidOid;
 	Oid			opcintype = InvalidOid;
@@ -1654,6 +1655,17 @@ FindFKPeriodOpers(Oid opclass,
 									 ANYMULTIRANGEOID,
 									 aggedcontainedbyoperoid,
 									 &strat);
+
+	switch (opcintype) {
+		case ANYRANGEOID:
+			*intersectoperoid = OID_RANGE_INTERSECT_RANGE_OP;
+			break;
+		case ANYMULTIRANGEOID:
+			*intersectoperoid = OID_MULTIRANGE_INTERSECT_MULTIRANGE_OP;
+			break;
+		default:
+			elog(ERROR, "Unexpected opcintype: %u", opcintype);
+	}
 }
 
 /*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index cbd1d9c56fc..dc1e7d4f439 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -10176,8 +10176,10 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 	{
 		Oid			periodoperoid;
 		Oid			aggedperiodoperoid;
+		Oid			intersectoperoid;
 
-		FindFKPeriodOpers(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid);
+		FindFKPeriodOpers(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid,
+						  &intersectoperoid);
 	}
 
 	/* First, create the constraint catalog entry itself. */
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 6896e1ae638..a06fcafdb7e 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -129,6 +129,7 @@ typedef struct RI_ConstraintInfo
 	Oid			ff_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (FK = FK) */
 	Oid			period_contained_by_oper;	/* anyrange <@ anyrange */
 	Oid			agged_period_contained_by_oper; /* fkattr <@ range_agg(pkattr) */
+	Oid			period_intersect_oper;	/* anyrange * anyrange */
 	dlist_node	valid_link;		/* Link in list of valid entries */
 } RI_ConstraintInfo;
 
@@ -731,8 +732,11 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 	 * not do anything.  However, this check should only be made in the NO
 	 * ACTION case; in RESTRICT cases we don't wish to allow another row to be
 	 * substituted.
+	 *
+	 * If the foreign key has PERIOD, we incorporate looking for replacement
+	 * rows in the main SQL query below, so we needn't do it here.
 	 */
-	if (is_no_action &&
+	if (is_no_action && !riinfo->hasperiod &&
 		ri_Check_Pk_Match(pk_rel, fk_rel, oldslot, riinfo))
 	{
 		table_close(fk_rel, RowShareLock);
@@ -750,8 +754,10 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 	if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
 	{
 		StringInfoData querybuf;
+		char		pkrelname[MAX_QUOTED_REL_NAME_LEN];
 		char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
 		char		attname[MAX_QUOTED_NAME_LEN];
+		char		periodattname[MAX_QUOTED_NAME_LEN];
 		char		paramname[16];
 		const char *querysep;
 		Oid			queryoids[RI_MAX_NUMKEYS];
@@ -763,6 +769,26 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 		 *		   FOR KEY SHARE OF x
 		 * The type id's for the $ parameters are those of the
 		 * corresponding PK attributes.
+		 *
+		 * For temporal foreign keys a reference could still be valid,
+		 * if the referenced range didn't change too much.
+		 * Also if the referencing time span extends past the current PK row,
+		 * we don't want to check that part: some other PK row should fulfill it.
+		 * We only want to validate the part matching the PK record we've changed.
+		 * Therefore to find invalid records we do this:
+		 * SELECT 1
+		 * FROM [ONLY] <fktable> x
+		 * WHERE $1 = x.fkatt1 [AND ...] AND $n && x.fkperiod
+		 * AND NOT coalesce((x.fkperiod * $n) <@
+		 *  (SELECT range_agg(r)
+		 *   FROM (SELECT y.pkperiod r
+		 *         FROM [ONLY] <pktable> y
+		 *         WHERE $1 = y.pkatt1 [AND ...] AND $n && y.pkperiod
+		 *         FOR KEY SHARE OF y), false)
+		 * FOR KEY SHARE OF x
+		 * We need the coalesce in case the first subquery returns no rows.
+		 * We need the second subquery because FOR KEY SHARE doesn't support
+		 * aggregate queries.
 		 * ----------
 		 */
 		initStringInfo(&querybuf);
@@ -791,6 +817,74 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 			querysep = "AND";
 			queryoids[i] = pk_type;
 		}
+
+		/*
+		 * Don't fail if the remaining history still fulfills the references.
+		 * Only check the part of the references intersecting with oldslot,
+		 * since the rest would be fulfilled by some other pk record.
+		 *
+		 * For NOACTION we can query the pk table and use whatever we find
+		 * (instead of calling ri_Check_Pk_Match above).
+		 */
+		if (riinfo->hasperiod && is_no_action)
+		{
+			Oid				pk_period_type = RIAttType(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]);
+			Oid				fk_period_type = RIAttType(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]);
+			StringInfoData	intersectbuf;
+			StringInfoData	replacementsbuf;
+			char *pk_only = pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ?
+				"" : "ONLY ";
+
+			quoteOneName(attname, RIAttName(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]));
+			sprintf(paramname, "$%d", riinfo->nkeys);
+
+			appendStringInfoString(&querybuf, " AND NOT coalesce(");
+
+			/* Intersect the fk with the old pk range */
+			initStringInfo(&intersectbuf);
+			appendStringInfoString(&intersectbuf, "(");
+			ri_GenerateQual(&intersectbuf, "",
+							attname, fk_period_type,
+							riinfo->period_intersect_oper,
+							paramname, pk_period_type);
+			appendStringInfoString(&intersectbuf, ")");
+
+			/* Find the remaining history */
+			initStringInfo(&replacementsbuf);
+			appendStringInfoString(&replacementsbuf, "(SELECT pg_catalog.range_agg(r) FROM ");
+
+			quoteOneName(periodattname, RIAttName(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]));
+			quoteRelationName(pkrelname, pk_rel);
+			appendStringInfo(&replacementsbuf, "(SELECT y.%s r FROM %s%s y",
+							 periodattname, pk_only, pkrelname);
+
+			/* Restrict pk rows to what matches */
+			querysep = "WHERE";
+			for (int i = 0; i < riinfo->nkeys; i++)
+			{
+				Oid		pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
+
+				quoteOneName(attname,
+							 RIAttName(pk_rel, riinfo->pk_attnums[i]));
+				sprintf(paramname, "$%d", i + 1);
+				ri_GenerateQual(&replacementsbuf, querysep,
+								paramname, pk_type,
+								riinfo->pp_eq_oprs[i],
+								attname, pk_type);
+				querysep = "AND";
+				queryoids[i] = pk_type;
+			}
+			appendStringInfoString(&replacementsbuf, " FOR KEY SHARE OF y)");
+			appendStringInfoString(&replacementsbuf, " y2)");
+
+			ri_GenerateQual(&querybuf, "",
+							intersectbuf.data, fk_period_type,
+							riinfo->agged_period_contained_by_oper,
+							replacementsbuf.data, ANYMULTIRANGEOID);
+			/* end of coalesce: */
+			appendStringInfoString(&querybuf, ", false)");
+		}
+
 		appendStringInfoString(&querybuf, " FOR KEY SHARE OF x");
 
 		/* Prepare and save the plan */
@@ -2262,7 +2356,8 @@ ri_LoadConstraintInfo(Oid constraintOid)
 
 		FindFKPeriodOpers(opclass,
 						  &riinfo->period_contained_by_oper,
-						  &riinfo->agged_period_contained_by_oper);
+						  &riinfo->agged_period_contained_by_oper,
+						  &riinfo->period_intersect_oper);
 	}
 
 	ReleaseSysCache(tup);
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 4b4476738a2..5edd4eb8a33 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -288,7 +288,8 @@ extern void DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
 									   int *num_fk_del_set_cols, AttrNumber *fk_del_set_cols);
 extern void FindFKPeriodOpers(Oid opclass,
 							  Oid *containedbyoperoid,
-							  Oid *aggedcontainedbyoperoid);
+							  Oid *aggedcontainedbyoperoid,
+							  Oid *intersectoperoid);
 
 extern bool check_functional_grouping(Oid relid,
 									  Index varno, Index varlevelsup,
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index 0e7511dde1c..aeddd292e2c 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -3150,7 +3150,8 @@
 { oid => '3899', descr => 'range difference',
   oprname => '-', oprleft => 'anyrange', oprright => 'anyrange',
   oprresult => 'anyrange', oprcode => 'range_minus' },
-{ oid => '3900', descr => 'range intersection',
+{ oid => '3900', oid_symbol => 'OID_RANGE_INTERSECT_RANGE_OP',
+  descr => 'range intersection',
   oprname => '*', oprleft => 'anyrange', oprright => 'anyrange',
   oprresult => 'anyrange', oprcom => '*(anyrange,anyrange)',
   oprcode => 'range_intersect' },
@@ -3417,7 +3418,8 @@
 { oid => '4393', descr => 'multirange minus',
   oprname => '-', oprleft => 'anymultirange', oprright => 'anymultirange',
   oprresult => 'anymultirange', oprcode => 'multirange_minus' },
-{ oid => '4394', descr => 'multirange intersect',
+{ oid => '4394', oid_symbol => 'OID_MULTIRANGE_INTERSECT_MULTIRANGE_OP',
+  descr => 'multirange intersect',
   oprname => '*', oprleft => 'anymultirange', oprright => 'anymultirange',
   oprresult => 'anymultirange', oprcom => '*(anymultirange,anymultirange)',
   oprcode => 'multirange_intersect' },
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index 319662bd58d..df955bd1b3c 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -1681,7 +1681,8 @@ DELETE FROM temporal_rng WHERE id = '[5,6)';
 INSERT INTO temporal_rng (id, valid_at) VALUES
   ('[5,6)', daterange('2018-01-01', '2018-02-01')),
   ('[5,6)', daterange('2018-02-01', '2018-03-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)');
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)');
 UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01')
 WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01');
 -- A PK update sliding the edge between two referenced rows:
@@ -1694,6 +1695,37 @@ UPDATE temporal_rng
 SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN daterange('2018-01-01', '2018-01-05')
                     WHEN lower(valid_at) = '2018-02-01' THEN daterange('2018-01-05', '2018-03-01') END
 WHERE id = '[6,7)';
+-- a PK update shrinking the referenced range but still valid:
+-- There are two references: one fulfilled by the first pk row,
+-- the other fulfilled by both pk rows combined.
+INSERT INTO temporal_rng (id, valid_at) VALUES
+  ('[1,2)', daterange('2018-01-01', '2018-03-01')),
+  ('[1,2)', daterange('2018-03-01', '2018-06-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[1,2)', daterange('2018-01-15', '2018-02-01'), '[1,2)'),
+  ('[2,3)', daterange('2018-01-15', '2018-05-01'), '[1,2)');
+UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update growing the referenced range is fine:
+UPDATE temporal_rng SET valid_at = daterange('2018-01-01', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date;
+-- a PK update shrinking the referenced range and changing the id invalidates the whole range:
+UPDATE temporal_rng SET id = '[2,3)', valid_at = daterange('2018-01-15', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+ERROR:  update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL:  Key (id, valid_at)=([1,2), [2018-01-01,2018-03-01)) is still referenced from table "temporal_fk_rng2rng".
+-- a PK update changing only the id invalidates the whole range:
+UPDATE temporal_rng SET id = '[2,3)'
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+ERROR:  update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL:  Key (id, valid_at)=([1,2), [2018-01-01,2018-03-01)) is still referenced from table "temporal_fk_rng2rng".
+-- a PK update that loses time from both ends, but is still valid:
+INSERT INTO temporal_rng (id, valid_at) VALUES
+  ('[2,3)', daterange('2018-01-01', '2018-03-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[3,4)', daterange('2018-01-15', '2018-02-01'), '[2,3)');
+UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-02-15')
+WHERE id = '[2,3)';
 -- a PK update that fails because both are referenced:
 UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
 WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
@@ -2169,7 +2201,8 @@ DELETE FROM temporal_mltrng WHERE id = '[5,6)';
 INSERT INTO temporal_mltrng (id, valid_at) VALUES
   ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))),
   ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01')));
-INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)');
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)');
 UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01'))
 WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01'));
 -- A PK update sliding the edge between two referenced rows:
@@ -2182,6 +2215,37 @@ UPDATE temporal_mltrng
 SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN datemultirange(daterange('2018-01-01', '2018-01-05'))
                     WHEN lower(valid_at) = '2018-02-01' THEN datemultirange(daterange('2018-01-05', '2018-03-01')) END
 WHERE id = '[6,7)';
+-- a PK update shrinking the referenced multirange but still valid:
+-- There are two references: one fulfilled by the first pk row,
+-- the other fulfilled by both pk rows combined.
+INSERT INTO temporal_mltrng (id, valid_at) VALUES
+  ('[1,2)', datemultirange(daterange('2018-01-01', '2018-03-01'))),
+  ('[1,2)', datemultirange(daterange('2018-03-01', '2018-06-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[1,2)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[1,2)'),
+  ('[2,3)', datemultirange(daterange('2018-01-15', '2018-05-01')), '[1,2)');
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update growing the referenced multirange is fine:
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-01', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date;
+-- a PK update shrinking the referenced multirange and changing the id invalidates the whole multirange:
+UPDATE temporal_mltrng SET id = '[2,3)', valid_at = datemultirange(daterange('2018-01-15', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+ERROR:  update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
+DETAIL:  Key (id, valid_at)=([1,2), {[2018-01-01,2018-03-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
+-- a PK update changing only the id invalidates the whole multirange:
+UPDATE temporal_mltrng SET id = '[2,3)'
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+ERROR:  update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
+DETAIL:  Key (id, valid_at)=([1,2), {[2018-01-01,2018-03-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
+-- a PK update that loses time from both ends, but is still valid:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES
+  ('[2,3)', datemultirange(daterange('2018-01-01', '2018-03-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[3,4)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[2,3)');
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-02-15'))
+WHERE id = '[2,3)';
 -- a PK update that fails because both are referenced:
 UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01'))
 WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index ebee2f2932e..9b844d1fa3b 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -1234,7 +1234,8 @@ DELETE FROM temporal_rng WHERE id = '[5,6)';
 INSERT INTO temporal_rng (id, valid_at) VALUES
   ('[5,6)', daterange('2018-01-01', '2018-02-01')),
   ('[5,6)', daterange('2018-02-01', '2018-03-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)');
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)');
 UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01')
 WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01');
 -- A PK update sliding the edge between two referenced rows:
@@ -1247,6 +1248,33 @@ UPDATE temporal_rng
 SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN daterange('2018-01-01', '2018-01-05')
                     WHEN lower(valid_at) = '2018-02-01' THEN daterange('2018-01-05', '2018-03-01') END
 WHERE id = '[6,7)';
+-- a PK update shrinking the referenced range but still valid:
+-- There are two references: one fulfilled by the first pk row,
+-- the other fulfilled by both pk rows combined.
+INSERT INTO temporal_rng (id, valid_at) VALUES
+  ('[1,2)', daterange('2018-01-01', '2018-03-01')),
+  ('[1,2)', daterange('2018-03-01', '2018-06-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[1,2)', daterange('2018-01-15', '2018-02-01'), '[1,2)'),
+  ('[2,3)', daterange('2018-01-15', '2018-05-01'), '[1,2)');
+UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update growing the referenced range is fine:
+UPDATE temporal_rng SET valid_at = daterange('2018-01-01', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date;
+-- a PK update shrinking the referenced range and changing the id invalidates the whole range:
+UPDATE temporal_rng SET id = '[2,3)', valid_at = daterange('2018-01-15', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update changing only the id invalidates the whole range:
+UPDATE temporal_rng SET id = '[2,3)'
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update that loses time from both ends, but is still valid:
+INSERT INTO temporal_rng (id, valid_at) VALUES
+  ('[2,3)', daterange('2018-01-01', '2018-03-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[3,4)', daterange('2018-01-15', '2018-02-01'), '[2,3)');
+UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-02-15')
+WHERE id = '[2,3)';
 -- a PK update that fails because both are referenced:
 UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
 WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
@@ -1691,7 +1719,8 @@ DELETE FROM temporal_mltrng WHERE id = '[5,6)';
 INSERT INTO temporal_mltrng (id, valid_at) VALUES
   ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))),
   ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01')));
-INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)');
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)');
 UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01'))
 WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01'));
 -- A PK update sliding the edge between two referenced rows:
@@ -1704,6 +1733,33 @@ UPDATE temporal_mltrng
 SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN datemultirange(daterange('2018-01-01', '2018-01-05'))
                     WHEN lower(valid_at) = '2018-02-01' THEN datemultirange(daterange('2018-01-05', '2018-03-01')) END
 WHERE id = '[6,7)';
+-- a PK update shrinking the referenced multirange but still valid:
+-- There are two references: one fulfilled by the first pk row,
+-- the other fulfilled by both pk rows combined.
+INSERT INTO temporal_mltrng (id, valid_at) VALUES
+  ('[1,2)', datemultirange(daterange('2018-01-01', '2018-03-01'))),
+  ('[1,2)', datemultirange(daterange('2018-03-01', '2018-06-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[1,2)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[1,2)'),
+  ('[2,3)', datemultirange(daterange('2018-01-15', '2018-05-01')), '[1,2)');
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update growing the referenced multirange is fine:
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-01', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date;
+-- a PK update shrinking the referenced multirange and changing the id invalidates the whole multirange:
+UPDATE temporal_mltrng SET id = '[2,3)', valid_at = datemultirange(daterange('2018-01-15', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update changing only the id invalidates the whole multirange:
+UPDATE temporal_mltrng SET id = '[2,3)'
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update that loses time from both ends, but is still valid:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES
+  ('[2,3)', datemultirange(daterange('2018-01-01', '2018-03-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[3,4)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[2,3)');
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-02-15'))
+WHERE id = '[2,3)';
 -- a PK update that fails because both are referenced:
 UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01'))
 WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
-- 
2.42.0



v44-0004-Fix-RESTRICT-temporal-foreign-keys-when-the-refe.patch (text/x-patch, 30.5K) download
From 12a84fe7881d9a8a60cbc68aa6af4f36eca8eb30 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 12 Nov 2024 09:19:44 -0800
Subject: [PATCH v44 4/4] Fix RESTRICT temporal foreign keys when the
 referenced endpoints change

A RESTICT foreign key must fail if the referenced key changes. A
temporal RESTRICT foreign key should fail if the key changes for a span
that is referenced. Changing the key for an unreferenced span is okay,
even if that row is referenced during some portion of its span that
didn't change. Therefore we have to compute which part(s) were lost, and
fail if we find any references overlapping those parts. We can still use
the same SQL as normal, because we need to adjust the PERIOD parameter
we search for. We can call without_portion to get the lost parts: they
are oldslot.pkperiod - newslot.pkperiod. This commit adds code for that
to ri_restrict, along with tests.

Author: Paul Jungwirth
---
 src/backend/catalog/pg_constraint.c           |  25 ++-
 src/backend/commands/tablecmds.c              |   6 +-
 src/backend/utils/adt/ri_triggers.c           | 161 +++++++++++++++---
 src/include/catalog/pg_constraint.h           |   9 +-
 .../regress/expected/without_overlaps.out     |  68 +++++++-
 src/test/regress/sql/without_overlaps.sql     |  60 ++++++-
 6 files changed, 292 insertions(+), 37 deletions(-)

diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index c7fdbeedc0a..0a8d741d650 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -1600,21 +1600,25 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
 }
 
 /*
- * FindFkPeriodOpers -
+ * FindFkPeriodOpersAndProcs -
  *
- * Looks up the operator oids used for the PERIOD part of a temporal foreign key.
+ * Looks up the operator and procedure oids used for the PERIOD part of a temporal foreign key.
  * The opclass should be the opclass of that PERIOD element.
  * Everything else is an output: containedbyoperoid is the ContainedBy operator for
  * types matching the PERIOD element.
  * aggedcontainedbyoperoid is also a ContainedBy operator,
  * but one whose rhs is a multirange.
  * That way foreign keys can compare fkattr <@ range_agg(pkattr).
+ * withoutportionoid is a set-returning function computing
+ * the difference between one range and another,
+ * returning each result range in a separate row.
  */
 void
-FindFKPeriodOpers(Oid opclass,
-				  Oid *containedbyoperoid,
-				  Oid *aggedcontainedbyoperoid,
-				  Oid *intersectoperoid)
+FindFKPeriodOpersAndProcs(Oid opclass,
+						  Oid *containedbyoperoid,
+						  Oid *aggedcontainedbyoperoid,
+						  Oid *intersectoperoid,
+						  Oid *withoutportionoid)
 {
 	Oid			opfamily = InvalidOid;
 	Oid			opcintype = InvalidOid;
@@ -1666,6 +1670,15 @@ FindFKPeriodOpers(Oid opclass,
 		default:
 			elog(ERROR, "Unexpected opcintype: %u", opcintype);
 	}
+
+	*withoutportionoid = InvalidOid;
+	*withoutportionoid = get_opfamily_proc(opfamily, opcintype, opcintype, GIST_WITHOUT_PORTION_PROC);
+	if (!OidIsValid(*withoutportionoid))
+		ereport(ERROR,
+				errcode(ERRCODE_UNDEFINED_OBJECT),
+				errmsg("could not identify a without_overlaps support function for type %s", format_type_be(opcintype)),
+				errhint("Define a without_overlaps support function for operator class \"%d\" for access method \"%s\".",
+						opclass, "gist"));
 }
 
 /*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index dc1e7d4f439..1c3c01eb072 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -10177,9 +10177,11 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 		Oid			periodoperoid;
 		Oid			aggedperiodoperoid;
 		Oid			intersectoperoid;
+		Oid			withoutoverlapsoid;
 
-		FindFKPeriodOpers(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid,
-						  &intersectoperoid);
+		FindFKPeriodOpersAndProcs(opclasses[numpks - 1], &periodoperoid,
+								  &aggedperiodoperoid, &intersectoperoid,
+								  &withoutoverlapsoid);
 	}
 
 	/* First, create the constraint catalog entry itself. */
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index a06fcafdb7e..1932955dea4 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -130,6 +130,7 @@ typedef struct RI_ConstraintInfo
 	Oid			period_contained_by_oper;	/* anyrange <@ anyrange */
 	Oid			agged_period_contained_by_oper; /* fkattr <@ range_agg(pkattr) */
 	Oid			period_intersect_oper;	/* anyrange * anyrange */
+	Oid			without_portion_proc;	/* anyrange - anyrange SRF */
 	dlist_node	valid_link;		/* Link in list of valid entries */
 } RI_ConstraintInfo;
 
@@ -207,7 +208,7 @@ static void ri_BuildQueryKey(RI_QueryKey *key,
 							 const RI_ConstraintInfo *riinfo,
 							 int32 constr_queryno);
 static bool ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot,
-						 const RI_ConstraintInfo *riinfo, bool rel_is_pk);
+						 const RI_ConstraintInfo *riinfo, bool rel_is_pk, bool skip_period);
 static bool ri_CompareWithCast(Oid eq_opr, Oid typeid,
 							   Datum lhs, Datum rhs);
 
@@ -229,6 +230,7 @@ static bool ri_PerformCheck(const RI_ConstraintInfo *riinfo,
 							RI_QueryKey *qkey, SPIPlanPtr qplan,
 							Relation fk_rel, Relation pk_rel,
 							TupleTableSlot *oldslot, TupleTableSlot *newslot,
+							int periodParam, Datum period,
 							bool detectNewRows, int expect_OK);
 static void ri_ExtractValues(Relation rel, TupleTableSlot *slot,
 							 const RI_ConstraintInfo *riinfo, bool rel_is_pk,
@@ -450,6 +452,7 @@ RI_FKey_check(TriggerData *trigdata)
 	ri_PerformCheck(riinfo, &qkey, qplan,
 					fk_rel, pk_rel,
 					NULL, newslot,
+					-1, (Datum) 0,
 					pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE,
 					SPI_OK_SELECT);
 
@@ -614,6 +617,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
 	result = ri_PerformCheck(riinfo, &qkey, qplan,
 							 fk_rel, pk_rel,
 							 oldslot, NULL,
+							 -1, (Datum) 0,
 							 true,	/* treat like update */
 							 SPI_OK_SELECT);
 
@@ -711,8 +715,18 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 	Relation	fk_rel;
 	Relation	pk_rel;
 	TupleTableSlot *oldslot;
+	TupleTableSlot *newslot;
 	RI_QueryKey qkey;
 	SPIPlanPtr	qplan;
+	AttrNumber	pkperiodattno = InvalidAttrNumber;
+	AttrNumber	fkperiodattno = InvalidAttrNumber;
+	int			targetRangeParam = -1;
+	Datum		targetRange = (Datum) 0;
+	FmgrInfo	flinfo;
+	ReturnSetInfo	rsi;
+	LOCAL_FCINFO(fcinfo, 2);
+	bool		multiplelost = false;
+	bool		finished = false;
 
 	riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
 									trigdata->tg_relation, true);
@@ -726,6 +740,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 	fk_rel = table_open(riinfo->fk_relid, RowShareLock);
 	pk_rel = trigdata->tg_relation;
 	oldslot = trigdata->tg_trigslot;
+	newslot = trigdata->tg_newslot;
 
 	/*
 	 * If another PK row now exists providing the old key values, we should
@@ -743,6 +758,12 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 		return PointerGetDatum(NULL);
 	}
 
+	if (riinfo->hasperiod)
+	{
+		pkperiodattno = riinfo->pk_attnums[riinfo->nkeys - 1];
+		fkperiodattno = riinfo->fk_attnums[riinfo->nkeys - 1];
+	}
+
 	SPI_connect();
 
 	/*
@@ -784,11 +805,21 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 		 *   FROM (SELECT y.pkperiod r
 		 *         FROM [ONLY] <pktable> y
 		 *         WHERE $1 = y.pkatt1 [AND ...] AND $n && y.pkperiod
-		 *         FOR KEY SHARE OF y), false)
+		 *         FOR KEY SHARE OF y) y2), false)
 		 * FOR KEY SHARE OF x
 		 * We need the coalesce in case the first subquery returns no rows.
 		 * We need the second subquery because FOR KEY SHARE doesn't support
 		 * aggregate queries.
+		 *
+		 * For RESTRICT keys we can't query pktable, so instead we use the old
+		 * and new periods to see what was removed, and look for references
+		 * matching that. If the scalar key part changed, then this is
+		 * (where $n is the old period and $2n the new):
+		 *   $n && x.fkperiod
+		 * But if the scalar key part didn't change, then we only lost part of
+		 * the time span, so we should look for:
+		 *   (SELECT range_agg(r) FROM without_portion($n, $2n) wo(r))
+		 *     && x.fkperiod
 		 * ----------
 		 */
 		initStringInfo(&querybuf);
@@ -828,14 +859,14 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 		 */
 		if (riinfo->hasperiod && is_no_action)
 		{
-			Oid				pk_period_type = RIAttType(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]);
-			Oid				fk_period_type = RIAttType(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]);
+			Oid				pk_period_type = RIAttType(pk_rel, pkperiodattno);
+			Oid				fk_period_type = RIAttType(fk_rel, fkperiodattno);
 			StringInfoData	intersectbuf;
 			StringInfoData	replacementsbuf;
 			char *pk_only = pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ?
 				"" : "ONLY ";
 
-			quoteOneName(attname, RIAttName(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]));
+			quoteOneName(attname, RIAttName(fk_rel, fkperiodattno));
 			sprintf(paramname, "$%d", riinfo->nkeys);
 
 			appendStringInfoString(&querybuf, " AND NOT coalesce(");
@@ -853,7 +884,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 			initStringInfo(&replacementsbuf);
 			appendStringInfoString(&replacementsbuf, "(SELECT pg_catalog.range_agg(r) FROM ");
 
-			quoteOneName(periodattname, RIAttName(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]));
+			quoteOneName(periodattname, RIAttName(pk_rel, pkperiodattno));
 			quoteRelationName(pkrelname, pk_rel);
 			appendStringInfo(&replacementsbuf, "(SELECT y.%s r FROM %s%s y",
 							 periodattname, pk_only, pkrelname);
@@ -874,8 +905,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 				querysep = "AND";
 				queryoids[i] = pk_type;
 			}
-			appendStringInfoString(&replacementsbuf, " FOR KEY SHARE OF y)");
-			appendStringInfoString(&replacementsbuf, " y2)");
+			appendStringInfoString(&replacementsbuf, " FOR KEY SHARE OF y) y2)");
 
 			ri_GenerateQual(&querybuf, "",
 							intersectbuf.data, fk_period_type,
@@ -894,12 +924,85 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 
 	/*
 	 * We have a plan now. Run it to check for existing references.
+	 *
+	 * Normally we only loop once here. But if
+	 * we have a RESTRICT constraint with a PERIOD,
+	 * we must only consider the timespan that was lost.
+	 *
+	 * If the scalar key part was UPDATEd,
+	 * then all of oldslot.pkperiod was lost
+	 * (whether the endpoints changed or not).
+	 * That's what we already check by default.
+	 *
+	 * Otherwise only oldslot.pkperiod - newslot.pkperiod was lost.
+	 * That may be more than one range, so we use the
+	 * without_portion set-returning function and loop
+	 * over its results. It also may be empty,
+	 * meaning nothing was lost, and no check is required.
+	 * We shouldn't be here if neither the scalar nor PERIOD part changed,
+	 * but it's easy to support anyway.
+	 *
+	 * For a DELETE, oldslot.pkperiod was lost,
+	 * which is what we check for by default.
 	 */
-	ri_PerformCheck(riinfo, &qkey, qplan,
-					fk_rel, pk_rel,
-					oldslot, NULL,
-					true,		/* must detect new rows */
-					SPI_OK_SELECT);
+	if (riinfo->hasperiod && !is_no_action)
+	{
+		if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)
+			&& ri_KeysEqual(pk_rel, oldslot, newslot, riinfo, true, true))
+		{
+			multiplelost = true;
+
+			fmgr_info(riinfo->without_portion_proc, &flinfo);
+			rsi.type = T_ReturnSetInfo;
+			rsi.econtext = CreateStandaloneExprContext();
+			rsi.expectedDesc = NULL;
+			rsi.allowedModes = (int) (SFRM_ValuePerCall);
+			rsi.returnMode = SFRM_ValuePerCall;
+			rsi.setResult = NULL;
+			rsi.setDesc = NULL;
+
+			InitFunctionCallInfoData(*fcinfo, &flinfo, 2, InvalidOid, NULL, (Node *) &rsi);
+			fcinfo->args[0].value = slot_getattr(oldslot, pkperiodattno,
+												 &fcinfo->args[0].isnull);
+			fcinfo->args[1].value = slot_getattr(newslot, pkperiodattno,
+												 &fcinfo->args[1].isnull);
+
+			targetRangeParam = riinfo->nkeys;
+		}
+	}
+
+	do
+	{
+		if (multiplelost)
+		{
+			/* Compute a span that was actually lost. */
+			targetRange = FunctionCallInvoke(fcinfo);
+
+			/*
+			 * If we have no more lost spans to check, we're done.
+			 * If no span was lost, we don't even need to check the foreign key.
+			 */
+			if (rsi.isDone == ExprEndResult)
+				break;
+
+			if (fcinfo->isnull)
+				elog(ERROR, "Get a null from without_portion function");
+		}
+		else
+			finished = true;
+
+		ri_PerformCheck(riinfo, &qkey, qplan,
+						fk_rel, pk_rel,
+						oldslot, NULL,
+						targetRangeParam, targetRange,
+						true,		/* must detect new rows */
+						SPI_OK_SELECT);
+
+	} while (!finished);
+
+	/* Free this before we shut down SPI since our memctx is a child */
+	if (multiplelost)
+		FreeExprContext(rsi.econtext, false);
 
 	if (SPI_finish() != SPI_OK_FINISH)
 		elog(ERROR, "SPI_finish failed");
@@ -1003,6 +1106,7 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
 	ri_PerformCheck(riinfo, &qkey, qplan,
 					fk_rel, pk_rel,
 					oldslot, NULL,
+					-1, (Datum) 0,
 					true,		/* must detect new rows */
 					SPI_OK_DELETE);
 
@@ -1123,6 +1227,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
 	ri_PerformCheck(riinfo, &qkey, qplan,
 					fk_rel, pk_rel,
 					oldslot, newslot,
+					-1, (Datum) 0,
 					true,		/* must detect new rows */
 					SPI_OK_UPDATE);
 
@@ -1354,6 +1459,7 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind)
 	ri_PerformCheck(riinfo, &qkey, qplan,
 					fk_rel, pk_rel,
 					oldslot, NULL,
+					-1, (Datum) 0,
 					true,		/* must detect new rows */
 					SPI_OK_UPDATE);
 
@@ -1411,7 +1517,7 @@ RI_FKey_pk_upd_check_required(Trigger *trigger, Relation pk_rel,
 		return false;
 
 	/* If all old and new key values are equal, no check is needed */
-	if (newslot && ri_KeysEqual(pk_rel, oldslot, newslot, riinfo, true))
+	if (newslot && ri_KeysEqual(pk_rel, oldslot, newslot, riinfo, true, false))
 		return false;
 
 	/* Else we need to fire the trigger. */
@@ -1504,7 +1610,7 @@ RI_FKey_fk_upd_check_required(Trigger *trigger, Relation fk_rel,
 		return true;
 
 	/* If all old and new key values are equal, no check is needed */
-	if (ri_KeysEqual(fk_rel, oldslot, newslot, riinfo, false))
+	if (ri_KeysEqual(fk_rel, oldslot, newslot, riinfo, false, false))
 		return false;
 
 	/* Else we need to fire the trigger. */
@@ -2354,10 +2460,11 @@ ri_LoadConstraintInfo(Oid constraintOid)
 	{
 		Oid			opclass = get_index_column_opclass(conForm->conindid, riinfo->nkeys);
 
-		FindFKPeriodOpers(opclass,
-						  &riinfo->period_contained_by_oper,
-						  &riinfo->agged_period_contained_by_oper,
-						  &riinfo->period_intersect_oper);
+		FindFKPeriodOpersAndProcs(opclass,
+								  &riinfo->period_contained_by_oper,
+								  &riinfo->agged_period_contained_by_oper,
+								  &riinfo->period_intersect_oper,
+								  &riinfo->without_portion_proc);
 	}
 
 	ReleaseSysCache(tup);
@@ -2500,6 +2607,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
 				RI_QueryKey *qkey, SPIPlanPtr qplan,
 				Relation fk_rel, Relation pk_rel,
 				TupleTableSlot *oldslot, TupleTableSlot *newslot,
+				int periodParam, Datum period,
 				bool detectNewRows, int expect_OK)
 {
 	Relation	query_rel,
@@ -2555,6 +2663,12 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
 		ri_ExtractValues(source_rel, oldslot, riinfo, source_is_pk,
 						 vals, nulls);
 	}
+	/* Add/replace a query param for the PERIOD if needed */
+	if (period)
+	{
+		vals[periodParam - 1] = period;
+		nulls[periodParam - 1] = ' ';
+	}
 
 	/*
 	 * In READ COMMITTED mode, we just need to use an up-to-date regular
@@ -2975,6 +3089,9 @@ ri_HashPreparedPlan(RI_QueryKey *key, SPIPlanPtr plan)
  * For normal FKs we check for equality.
  * For temporal FKs we check that the PK side is a superset of its old value,
  * or the FK side is a subset of its old value.
+ * If skip_period is set, we ignore the last key element.
+ * This lets us ask if the scalar key parts changed,
+ * ignoring the PERIOD.
  *
  * Note: at some point we might wish to redefine this as checking for
  * "IS NOT DISTINCT" rather than "=", that is, allow two nulls to be
@@ -2983,17 +3100,19 @@ ri_HashPreparedPlan(RI_QueryKey *key, SPIPlanPtr plan)
  */
 static bool
 ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot,
-			 const RI_ConstraintInfo *riinfo, bool rel_is_pk)
+			 const RI_ConstraintInfo *riinfo, bool rel_is_pk, bool skip_period)
 {
 	const int16 *attnums;
 
+	Assert(skip_period ? riinfo->hasperiod : true);
+
 	if (rel_is_pk)
 		attnums = riinfo->pk_attnums;
 	else
 		attnums = riinfo->fk_attnums;
 
 	/* XXX: could be worthwhile to fetch all necessary attrs at once */
-	for (int i = 0; i < riinfo->nkeys; i++)
+	for (int i = 0; i < riinfo->nkeys - (skip_period ? 1 : 0); i++)
 	{
 		Datum		oldvalue;
 		Datum		newvalue;
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 5edd4eb8a33..5f25c5a0065 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -286,10 +286,11 @@ extern void DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
 									   AttrNumber *conkey, AttrNumber *confkey,
 									   Oid *pf_eq_oprs, Oid *pp_eq_oprs, Oid *ff_eq_oprs,
 									   int *num_fk_del_set_cols, AttrNumber *fk_del_set_cols);
-extern void FindFKPeriodOpers(Oid opclass,
-							  Oid *containedbyoperoid,
-							  Oid *aggedcontainedbyoperoid,
-							  Oid *intersectoperoid);
+extern void FindFKPeriodOpersAndProcs(Oid opclass,
+									  Oid *containedbyoperoid,
+									  Oid *aggedcontainedbyoperoid,
+									  Oid *intersectoperoid,
+									  Oid *withoutportionoid);
 
 extern bool check_functional_grouping(Oid relid,
 									  Index varno, Index varlevelsup,
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index df955bd1b3c..1ab7787041f 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -1769,7 +1769,8 @@ DELETE FROM temporal_rng WHERE id = '[5,6)';
 INSERT INTO temporal_rng (id, valid_at) VALUES
   ('[5,6)', daterange('2018-01-01', '2018-02-01')),
   ('[5,6)', daterange('2018-02-01', '2018-03-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)');
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)');
 UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01')
 WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01');
 -- A PK update sliding the edge between two referenced rows:
@@ -1784,6 +1785,37 @@ SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN daterange('2018-01-
 WHERE id = '[6,7)';
 ERROR:  update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
 DETAIL:  Key (id, valid_at)=([6,7), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
+-- a PK update shrinking the referenced range but still valid:
+-- There are two references: one fulfilled by the first pk row,
+-- the other fulfilled by both pk rows combined.
+INSERT INTO temporal_rng (id, valid_at) VALUES
+  ('[1,2)', daterange('2018-01-01', '2018-03-01')),
+  ('[1,2)', daterange('2018-03-01', '2018-06-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[1,2)', daterange('2018-01-15', '2018-02-01'), '[1,2)'),
+  ('[2,3)', daterange('2018-01-15', '2018-05-01'), '[1,2)');
+UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update growing the referenced range is fine:
+UPDATE temporal_rng SET valid_at = daterange('2018-01-01', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date;
+-- a PK update shrinking the referenced range and changing the id invalidates the whole range:
+UPDATE temporal_rng SET id = '[2,3)', valid_at = daterange('2018-01-15', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+ERROR:  update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL:  Key (id, valid_at)=([1,2), [2018-01-01,2018-03-01)) is still referenced from table "temporal_fk_rng2rng".
+-- a PK update changing only the id invalidates the whole range:
+UPDATE temporal_rng SET id = '[2,3)'
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+ERROR:  update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL:  Key (id, valid_at)=([1,2), [2018-01-01,2018-03-01)) is still referenced from table "temporal_fk_rng2rng".
+-- a PK update that loses time from both ends, but is still valid:
+INSERT INTO temporal_rng (id, valid_at) VALUES
+  ('[2,3)', daterange('2018-01-01', '2018-03-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[3,4)', daterange('2018-01-15', '2018-02-01'), '[2,3)');
+UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-02-15')
+WHERE id = '[2,3)';
 -- a PK update that fails because both are referenced (even before commit):
 BEGIN;
   ALTER TABLE temporal_fk_rng2rng
@@ -2285,7 +2317,8 @@ DELETE FROM temporal_mltrng WHERE id = '[5,6)';
 INSERT INTO temporal_mltrng (id, valid_at) VALUES
   ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))),
   ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01')));
-INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)');
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)');
 UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01'))
 WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01'));
 -- A PK update sliding the edge between two referenced rows:
@@ -2300,6 +2333,37 @@ SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN datemultirange(date
 WHERE id = '[6,7)';
 ERROR:  update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
 DETAIL:  Key (id, valid_at)=([6,7), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
+-- a PK update shrinking the referenced multirange but still valid:
+-- There are two references: one fulfilled by the first pk row,
+-- the other fulfilled by both pk rows combined.
+INSERT INTO temporal_mltrng (id, valid_at) VALUES
+  ('[1,2)', datemultirange(daterange('2018-01-01', '2018-03-01'))),
+  ('[1,2)', datemultirange(daterange('2018-03-01', '2018-06-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[1,2)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[1,2)'),
+  ('[2,3)', datemultirange(daterange('2018-01-15', '2018-05-01')), '[1,2)');
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update growing the referenced multirange is fine:
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-01', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date;
+-- a PK update shrinking the referenced multirange and changing the id invalidates the whole multirange:
+UPDATE temporal_mltrng SET id = '[2,3)', valid_at = datemultirange(daterange('2018-01-15', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+ERROR:  update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
+DETAIL:  Key (id, valid_at)=([1,2), {[2018-01-01,2018-03-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
+-- a PK update changing only the id invalidates the whole multirange:
+UPDATE temporal_mltrng SET id = '[2,3)'
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+ERROR:  update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
+DETAIL:  Key (id, valid_at)=([1,2), {[2018-01-01,2018-03-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
+-- a PK update that loses time from both ends, but is still valid:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES
+  ('[2,3)', datemultirange(daterange('2018-01-01', '2018-03-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[3,4)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[2,3)');
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-02-15'))
+WHERE id = '[2,3)';
 -- a PK update that fails because both are referenced (even before commit):
 BEGIN;
   ALTER TABLE temporal_fk_mltrng2mltrng
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 9b844d1fa3b..b6201330112 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -1315,7 +1315,8 @@ DELETE FROM temporal_rng WHERE id = '[5,6)';
 INSERT INTO temporal_rng (id, valid_at) VALUES
   ('[5,6)', daterange('2018-01-01', '2018-02-01')),
   ('[5,6)', daterange('2018-02-01', '2018-03-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)');
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)');
 UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01')
 WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01');
 -- A PK update sliding the edge between two referenced rows:
@@ -1328,6 +1329,33 @@ UPDATE temporal_rng
 SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN daterange('2018-01-01', '2018-01-05')
                     WHEN lower(valid_at) = '2018-02-01' THEN daterange('2018-01-05', '2018-03-01') END
 WHERE id = '[6,7)';
+-- a PK update shrinking the referenced range but still valid:
+-- There are two references: one fulfilled by the first pk row,
+-- the other fulfilled by both pk rows combined.
+INSERT INTO temporal_rng (id, valid_at) VALUES
+  ('[1,2)', daterange('2018-01-01', '2018-03-01')),
+  ('[1,2)', daterange('2018-03-01', '2018-06-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[1,2)', daterange('2018-01-15', '2018-02-01'), '[1,2)'),
+  ('[2,3)', daterange('2018-01-15', '2018-05-01'), '[1,2)');
+UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update growing the referenced range is fine:
+UPDATE temporal_rng SET valid_at = daterange('2018-01-01', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date;
+-- a PK update shrinking the referenced range and changing the id invalidates the whole range:
+UPDATE temporal_rng SET id = '[2,3)', valid_at = daterange('2018-01-15', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update changing only the id invalidates the whole range:
+UPDATE temporal_rng SET id = '[2,3)'
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update that loses time from both ends, but is still valid:
+INSERT INTO temporal_rng (id, valid_at) VALUES
+  ('[2,3)', daterange('2018-01-01', '2018-03-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[3,4)', daterange('2018-01-15', '2018-02-01'), '[2,3)');
+UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-02-15')
+WHERE id = '[2,3)';
 -- a PK update that fails because both are referenced (even before commit):
 BEGIN;
   ALTER TABLE temporal_fk_rng2rng
@@ -1796,7 +1824,8 @@ DELETE FROM temporal_mltrng WHERE id = '[5,6)';
 INSERT INTO temporal_mltrng (id, valid_at) VALUES
   ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))),
   ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01')));
-INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)');
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)');
 UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01'))
 WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01'));
 -- A PK update sliding the edge between two referenced rows:
@@ -1809,6 +1838,33 @@ UPDATE temporal_mltrng
 SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN datemultirange(daterange('2018-01-01', '2018-01-05'))
                     WHEN lower(valid_at) = '2018-02-01' THEN datemultirange(daterange('2018-01-05', '2018-03-01')) END
 WHERE id = '[6,7)';
+-- a PK update shrinking the referenced multirange but still valid:
+-- There are two references: one fulfilled by the first pk row,
+-- the other fulfilled by both pk rows combined.
+INSERT INTO temporal_mltrng (id, valid_at) VALUES
+  ('[1,2)', datemultirange(daterange('2018-01-01', '2018-03-01'))),
+  ('[1,2)', datemultirange(daterange('2018-03-01', '2018-06-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[1,2)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[1,2)'),
+  ('[2,3)', datemultirange(daterange('2018-01-15', '2018-05-01')), '[1,2)');
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update growing the referenced multirange is fine:
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-01', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date;
+-- a PK update shrinking the referenced multirange and changing the id invalidates the whole multirange:
+UPDATE temporal_mltrng SET id = '[2,3)', valid_at = datemultirange(daterange('2018-01-15', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update changing only the id invalidates the whole multirange:
+UPDATE temporal_mltrng SET id = '[2,3)'
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update that loses time from both ends, but is still valid:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES
+  ('[2,3)', datemultirange(daterange('2018-01-01', '2018-03-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[3,4)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[2,3)');
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-02-15'))
+WHERE id = '[2,3)';
 -- a PK update that fails because both are referenced (even before commit):
 BEGIN;
   ALTER TABLE temporal_fk_mltrng2mltrng
-- 
2.42.0