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.

(previously committed as 46a0cd4cefb, reverted by 46a0cd4cefb; the new
part is this:)

Because 'empty' && 'empty' is false, the temporal PK/UQ constraint
allowed duplicates, which is confusing to users and breaks internal
expectations.  For instance, when GROUP BY checks functional
dependencies on the PK, it allows selecting other columns from the
table, but in the presence of duplicate keys you could get the value
from any of their rows.  So we need to forbid empties.

This all means that at the moment we can only support ranges and
multiranges for temporal PK/UQs, unlike the original patch (above).
Documentation and tests for this are added.  But this could
conceivably be extended by introducing some more general support for
the notion of "empty" for other types.

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

1 files changed, 65 insertions, 1 deletions

diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c
index 403a3f40551..f9a2fac79e4 100644
--- a/src/backend/executor/execIndexing.c
+++ b/src/backend/executor/execIndexing.c
@@ -114,6 +114,8 @@
 #include "executor/executor.h"
 #include "nodes/nodeFuncs.h"
 #include "storage/lmgr.h"
+#include "utils/multirangetypes.h"
+#include "utils/rangetypes.h"
 #include "utils/snapmgr.h"
 
 /* waitMode argument to check_exclusion_or_unique_constraint() */
@@ -141,6 +143,8 @@ static bool index_unchanged_by_update(ResultRelInfo *resultRelInfo,
 									  Relation indexRelation);
 static bool index_expression_changed_walker(Node *node,
 											Bitmapset *allUpdatedCols);
+static void ExecWithoutOverlapsNotEmpty(Relation rel, NameData attname, Datum attval,
+										char typtype, Oid atttypid);
 
 /* ----------------------------------------------------------------
  *		ExecOpenIndices
@@ -211,7 +215,7 @@ ExecOpenIndices(ResultRelInfo *resultRelInfo, bool speculative)
 		 * detection in logical replication, add extra information required by
 		 * unique index entries.
 		 */
-		if (speculative && ii->ii_Unique)
+		if (speculative && ii->ii_Unique && !indexDesc->rd_index->indisexclusion)
 			BuildSpeculativeIndexInfo(indexDesc, ii);
 
 		relationDescs[i] = indexDesc;
@@ -726,6 +730,32 @@ check_exclusion_or_unique_constraint(Relation heap, Relation index,
 	}
 
 	/*
+	 * If this is a WITHOUT OVERLAPS constraint, we must also forbid empty
+	 * ranges/multiranges. This must happen before we look for NULLs below, or
+	 * a UNIQUE constraint could insert an empty range along with a NULL
+	 * scalar part.
+	 */
+	if (indexInfo->ii_WithoutOverlaps)
+	{
+		/*
+		 * Look up the type from the heap tuple, but check the Datum from the
+		 * index tuple.
+		 */
+		AttrNumber	attno = indexInfo->ii_IndexAttrNumbers[indnkeyatts - 1];
+
+		if (!isnull[indnkeyatts - 1])
+		{
+			TupleDesc	tupdesc = RelationGetDescr(heap);
+			Form_pg_attribute att = TupleDescAttr(tupdesc, attno - 1);
+			TypeCacheEntry *typcache = lookup_type_cache(att->atttypid, 0);
+
+			ExecWithoutOverlapsNotEmpty(heap, att->attname,
+										values[indnkeyatts - 1],
+										typcache->typtype, att->atttypid);
+		}
+	}
+
+	/*
 	 * If any of the input values are NULL, and the index uses the default
 	 * nulls-are-distinct mode, the constraint check is assumed to pass (i.e.,
 	 * we assume the operators are strict).  Otherwise, we interpret the
@@ -1102,3 +1132,37 @@ index_expression_changed_walker(Node *node, Bitmapset *allUpdatedCols)
 	return expression_tree_walker(node, index_expression_changed_walker,
 								  (void *) allUpdatedCols);
 }
+
+/*
+ * ExecWithoutOverlapsNotEmpty - raise an error if the tuple has an empty
+ * range or multirange in the given attribute.
+ */
+static void
+ExecWithoutOverlapsNotEmpty(Relation rel, NameData attname, Datum attval, char typtype, Oid atttypid)
+{
+	bool		isempty;
+	RangeType  *r;
+	MultirangeType *mr;
+
+	switch (typtype)
+	{
+		case TYPTYPE_RANGE:
+			r = DatumGetRangeTypeP(attval);
+			isempty = RangeIsEmpty(r);
+			break;
+		case TYPTYPE_MULTIRANGE:
+			mr = DatumGetMultirangeTypeP(attval);
+			isempty = MultirangeIsEmpty(mr);
+			break;
+		default:
+			elog(ERROR, "WITHOUT OVERLAPS column \"%s\" is not a range or multirange",
+				 NameStr(attname));
+	}
+
+	/* Report a CHECK_VIOLATION */
+	if (isempty)
+		ereport(ERROR,
+				(errcode(ERRCODE_CHECK_VIOLATION),
+				 errmsg("empty WITHOUT OVERLAPS value found in column \"%s\" in relation \"%s\"",
+						NameStr(attname), RelationGetRelationName(rel))));
+}