Add temporal PRIMARY KEY and UNIQUE constraints

Add WITHOUT OVERLAPS clause to PRIMARY KEY and UNIQUE constraints.
These are backed by GiST indexes instead of B-tree indexes, since they
are essentially exclusion constraints with = for the scalar parts of
the key and && for the temporal part.

Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com

4 files changed, 71 insertions, 1 deletions

diff --git a/contrib/btree_gist/Makefile b/contrib/btree_gist/Makefile
index 9ab8548bc0e..7ac2df26c10 100644
--- a/contrib/btree_gist/Makefile
+++ b/contrib/btree_gist/Makefile
@@ -40,7 +40,7 @@ PGFILEDESC = "btree_gist - B-tree equivalent GiST operator classes"
 REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz \
         time timetz date interval macaddr macaddr8 inet cidr text varchar char \
         bytea bit varbit numeric uuid not_equal enum bool partitions \
-        stratnum
+        stratnum without_overlaps
 
 SHLIB_LINK += $(filter -lm, $(LIBS))
 
diff --git a/contrib/btree_gist/expected/without_overlaps.out b/contrib/btree_gist/expected/without_overlaps.out
new file mode 100644
index 00000000000..be52c522e89
--- /dev/null
+++ b/contrib/btree_gist/expected/without_overlaps.out
@@ -0,0 +1,44 @@
+-- Core must test WITHOUT OVERLAPS
+-- with an int4range + daterange,
+-- so here we do some simple tests
+-- to make sure int + daterange works too,
+-- since that is the expected use-case.
+CREATE TABLE temporal_rng (
+  id integer,
+  valid_at daterange,
+  CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_rng
+              Table "public.temporal_rng"
+  Column  |   Type    | Collation | Nullable | Default 
+----------+-----------+-----------+----------+---------
+ id       | integer   |           | not null | 
+ valid_at | daterange |           | not null | 
+Indexes:
+    "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
+            pg_get_constraintdef             
+---------------------------------------------
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+(1 row)
+
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
+                                pg_get_indexdef                                
+-------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at)
+(1 row)
+
+INSERT INTO temporal_rng VALUES
+  (1, '[2000-01-01,2001-01-01)');
+-- same key, doesn't overlap:
+INSERT INTO temporal_rng VALUES
+  (1, '[2001-01-01,2002-01-01)');
+-- overlaps but different key:
+INSERT INTO temporal_rng VALUES
+  (2, '[2000-01-01,2001-01-01)');
+-- should fail:
+INSERT INTO temporal_rng VALUES
+  (1, '[2000-06-01,2001-01-01)');
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng_pk"
+DETAIL:  Key (id, valid_at)=(1, [06-01-2000,01-01-2001)) conflicts with existing key (id, valid_at)=(1, [01-01-2000,01-01-2001)).
diff --git a/contrib/btree_gist/meson.build b/contrib/btree_gist/meson.build
index a44ce905e59..73b1bbf52a6 100644
--- a/contrib/btree_gist/meson.build
+++ b/contrib/btree_gist/meson.build
@@ -91,6 +91,7 @@ tests += {
       'bool',
       'partitions',
       'stratnum',
+      'without_overlaps',
     ],
   },
 }
diff --git a/contrib/btree_gist/sql/without_overlaps.sql b/contrib/btree_gist/sql/without_overlaps.sql
new file mode 100644
index 00000000000..433c609ab22
--- /dev/null
+++ b/contrib/btree_gist/sql/without_overlaps.sql
@@ -0,0 +1,25 @@
+-- Core must test WITHOUT OVERLAPS
+-- with an int4range + daterange,
+-- so here we do some simple tests
+-- to make sure int + daterange works too,
+-- since that is the expected use-case.
+CREATE TABLE temporal_rng (
+  id integer,
+  valid_at daterange,
+  CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_rng
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
+
+INSERT INTO temporal_rng VALUES
+  (1, '[2000-01-01,2001-01-01)');
+-- same key, doesn't overlap:
+INSERT INTO temporal_rng VALUES
+  (1, '[2001-01-01,2002-01-01)');
+-- overlaps but different key:
+INSERT INTO temporal_rng VALUES
+  (2, '[2000-01-01,2001-01-01)');
+-- should fail:
+INSERT INTO temporal_rng VALUES
+  (1, '[2000-06-01,2001-01-01)');