v1-0001-forbid-empty-range-value-for-period-column.patch

From d8721a13a2575f2e74841168b12dc018c90a0c66 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 9 Jul 2024 15:14:10 +0800
Subject: [PATCH v1 1/1] forbid empty range value for period column.

if one column have WITHOUT OVERLAPS attribute,
then forbid this column to have 'empty' range value.
---
 src/backend/commands/indexcmds.c              |  4 +-
 src/backend/utils/adt/multirangetypes.c       | 35 ++++++++++++++
 src/backend/utils/adt/rangetypes.c            | 46 +++++++++++++++++++
 src/backend/utils/adt/rangetypes_gist.c       |  8 ++++
 src/include/access/stratnum.h                 |  3 +-
 src/include/catalog/pg_amop.dat               |  6 +++
 src/include/catalog/pg_operator.dat           | 11 +++++
 src/include/catalog/pg_proc.dat               |  8 ++++
 src/include/utils/multirangetypes.h           |  4 ++
 src/include/utils/rangetypes.h                |  3 ++
 src/test/regress/expected/opr_sanity.out      |  6 ++-
 .../regress/expected/without_overlaps.out     |  2 +
 src/test/regress/sql/without_overlaps.sql     |  2 +
 13 files changed, 133 insertions(+), 5 deletions(-)

diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 7a87626f..acce7378 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -2178,7 +2178,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
 			Oid			opid;
 
 			if (attn == nkeycols - 1)
-				strat = RTOverlapStrategyNumber;
+				strat = RTPeriodOverlapStrategyNumber;
 			else
 				strat = RTEqualStrategyNumber;
 			GetOperatorFromWellKnownStrategy(opclassOids[attn], atttype,
@@ -2441,7 +2441,7 @@ GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype,
 	Oid			opcintype;
 	StrategyNumber instrat = *strat;
 
-	Assert(instrat == RTEqualStrategyNumber || instrat == RTOverlapStrategyNumber);
+	Assert(instrat == RTEqualStrategyNumber || instrat == RTOverlapStrategyNumber || instrat == RTPeriodOverlapStrategyNumber);
 
 	*opid = InvalidOid;
 
diff --git a/src/backend/utils/adt/multirangetypes.c b/src/backend/utils/adt/multirangetypes.c
index f82e6f42..d30d6291 100644
--- a/src/backend/utils/adt/multirangetypes.c
+++ b/src/backend/utils/adt/multirangetypes.c
@@ -1969,6 +1969,19 @@ multirange_overlaps_multirange(PG_FUNCTION_ARGS)
 	PG_RETURN_BOOL(multirange_overlaps_multirange_internal(typcache->rngtype, mr1, mr2));
 }
 
+
+Datum
+period_multirange_overlaps_multirange(PG_FUNCTION_ARGS)
+{
+	MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0);
+	MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1);
+	TypeCacheEntry *typcache;
+
+	typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr1));
+
+	PG_RETURN_BOOL(multirange_overlaps_multirange_internal(typcache->rngtype, mr1, mr2));
+}
+
 /*
  * Comparison function for checking if any range of multirange overlaps given
  * key range using binary search.
@@ -1990,6 +2003,28 @@ multirange_range_overlaps_bsearch_comparison(TypeCacheEntry *typcache,
 	return 0;
 }
 
+bool
+period_overlaps_multirange_internal(TypeCacheEntry *rangetyp,
+								   const RangeType *r,
+								   const MultirangeType *mr)
+{
+	RangeBound	bounds[2];
+	bool		empty;
+
+	/*
+	 * Empties never overlap, even with empties. (This seems strange since
+	 * they *do* contain each other, but we want to follow how ranges work.)
+	 */
+	if (RangeIsEmpty(r) || MultirangeIsEmpty(mr))
+		elog(ERROR, "multirange types cannot be empty in this case");
+
+	range_deserialize(rangetyp, r, &bounds[0], &bounds[1], &empty);
+	Assert(!empty);
+
+	return multirange_bsearch_match(rangetyp, mr, bounds,
+									multirange_range_overlaps_bsearch_comparison);
+}
+
 bool
 range_overlaps_multirange_internal(TypeCacheEntry *rangetyp,
 								   const RangeType *r,
diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index 2d94a6b8..42f72341 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -869,6 +869,39 @@ range_overlaps_internal(TypeCacheEntry *typcache, const RangeType *r1, const Ran
 	return false;
 }
 
+/* overlaps? (internal version) */
+bool
+period_overlaps_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2)
+{
+	RangeBound	lower1,
+				lower2;
+	RangeBound	upper1,
+				upper2;
+	bool		empty1,
+				empty2;
+
+	/* Different types should be prevented by ANYRANGE matching rules */
+	if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2))
+		elog(ERROR, "range types do not match");
+
+	range_deserialize(typcache, r1, &lower1, &upper1, &empty1);
+	range_deserialize(typcache, r2, &lower2, &upper2, &empty2);
+
+	/* An empty range does not overlap any other range */
+	if (empty1 || empty2)
+		elog(ERROR, "period cannot be empty");
+
+	if (range_cmp_bounds(typcache, &lower1, &lower2) >= 0 &&
+		range_cmp_bounds(typcache, &lower1, &upper2) <= 0)
+		return true;
+
+	if (range_cmp_bounds(typcache, &lower2, &lower1) >= 0 &&
+		range_cmp_bounds(typcache, &lower2, &upper1) <= 0)
+		return true;
+
+	return false;
+}
+
 /* overlaps? */
 Datum
 range_overlaps(PG_FUNCTION_ARGS)
@@ -882,6 +915,19 @@ range_overlaps(PG_FUNCTION_ARGS)
 	PG_RETURN_BOOL(range_overlaps_internal(typcache, r1, r2));
 }
 
+/* period (range that does have empty range value) overlaps? */
+Datum
+period_overlaps(PG_FUNCTION_ARGS)
+{
+	RangeType  *r1 = PG_GETARG_RANGE_P(0);
+	RangeType  *r2 = PG_GETARG_RANGE_P(1);
+	TypeCacheEntry *typcache;
+
+	typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1));
+
+	PG_RETURN_BOOL(period_overlaps_internal(typcache, r1, r2));
+}
+
 /* does not extend to right of? (internal version) */
 bool
 range_overleft_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2)
diff --git a/src/backend/utils/adt/rangetypes_gist.c b/src/backend/utils/adt/rangetypes_gist.c
index cb28e985..ac60def4 100644
--- a/src/backend/utils/adt/rangetypes_gist.c
+++ b/src/backend/utils/adt/rangetypes_gist.c
@@ -929,6 +929,8 @@ range_gist_consistent_int_range(TypeCacheEntry *typcache,
 			return (!range_after_internal(typcache, key, query));
 		case RANGESTRAT_OVERLAPS:
 			return range_overlaps_internal(typcache, key, query);
+		case PERIODSTRAT_OVERLAPS:
+			return period_overlaps_internal(typcache, key, query);
 		case RANGESTRAT_OVERRIGHT:
 			if (RangeIsEmpty(key) || RangeIsEmpty(query))
 				return false;
@@ -989,6 +991,8 @@ range_gist_consistent_int_multirange(TypeCacheEntry *typcache,
 			if (RangeIsEmpty(key) || MultirangeIsEmpty(query))
 				return false;
 			return (!range_after_multirange_internal(typcache, key, query));
+		case PERIODSTRAT_OVERLAPS:
+			return period_overlaps_multirange_internal(typcache, key, query);
 		case RANGESTRAT_OVERLAPS:
 			return range_overlaps_multirange_internal(typcache, key, query);
 		case RANGESTRAT_OVERRIGHT:
@@ -1066,6 +1070,8 @@ range_gist_consistent_leaf_range(TypeCacheEntry *typcache,
 			return range_before_internal(typcache, key, query);
 		case RANGESTRAT_OVERLEFT:
 			return range_overleft_internal(typcache, key, query);
+		case PERIODSTRAT_OVERLAPS:
+			return period_overlaps_internal(typcache, key, query);
 		case RANGESTRAT_OVERLAPS:
 			return range_overlaps_internal(typcache, key, query);
 		case RANGESTRAT_OVERRIGHT:
@@ -1103,6 +1109,8 @@ range_gist_consistent_leaf_multirange(TypeCacheEntry *typcache,
 			return range_overleft_multirange_internal(typcache, key, query);
 		case RANGESTRAT_OVERLAPS:
 			return range_overlaps_multirange_internal(typcache, key, query);
+		case PERIODSTRAT_OVERLAPS:
+			return period_overlaps_multirange_internal(typcache, key, query);
 		case RANGESTRAT_OVERRIGHT:
 			return range_overright_multirange_internal(typcache, key, query);
 		case RANGESTRAT_AFTER:
diff --git a/src/include/access/stratnum.h b/src/include/access/stratnum.h
index 8a47d3c9..94737af4 100644
--- a/src/include/access/stratnum.h
+++ b/src/include/access/stratnum.h
@@ -78,8 +78,9 @@ typedef uint16 StrategyNumber;
 #define RTPrefixStrategyNumber			28	/* for text ^@ */
 #define RTOldBelowStrategyNumber		29	/* for old spelling of <<| */
 #define RTOldAboveStrategyNumber		30	/* for old spelling of |>> */
+#define RTPeriodOverlapStrategyNumber   31	/* for special gist &&& */
 
-#define RTMaxStrategyNumber				30
+#define RTMaxStrategyNumber				31
 
 
 #endif							/* STRATNUM_H */
diff --git a/src/include/catalog/pg_amop.dat b/src/include/catalog/pg_amop.dat
index d8a05214..96590c32 100644
--- a/src/include/catalog/pg_amop.dat
+++ b/src/include/catalog/pg_amop.dat
@@ -1358,6 +1358,9 @@
 { amopfamily => 'gist/range_ops', amoplefttype => 'anyrange',
   amoprighttype => 'anyrange', amopstrategy => '3',
   amopopr => '&&(anyrange,anyrange)', amopmethod => 'gist' },
+{ amopfamily => 'gist/range_ops', amoplefttype => 'anyrange',
+  amoprighttype => 'anyrange', amopstrategy => '31',
+  amopopr => '&&&(anyrange,anyrange)', amopmethod => 'gist' },
 { amopfamily => 'gist/range_ops', amoplefttype => 'anyrange',
   amoprighttype => 'anymultirange', amopstrategy => '3',
   amopopr => '&&(anyrange,anymultirange)', amopmethod => 'gist' },
@@ -1414,6 +1417,9 @@
 { amopfamily => 'gist/multirange_ops', amoplefttype => 'anymultirange',
   amoprighttype => 'anymultirange', amopstrategy => '3',
   amopopr => '&&(anymultirange,anymultirange)', amopmethod => 'gist' },
+{ amopfamily => 'gist/multirange_ops', amoplefttype => 'anymultirange',
+  amoprighttype => 'anymultirange', amopstrategy => '31',
+  amopopr => '&&&(anymultirange,anymultirange)', amopmethod => 'gist' },
 { amopfamily => 'gist/multirange_ops', amoplefttype => 'anymultirange',
   amoprighttype => 'anyrange', amopstrategy => '3',
   amopopr => '&&(anymultirange,anyrange)', amopmethod => 'gist' },
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index 0e7511dd..4022b9f7 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -3095,6 +3095,11 @@
   oprresult => 'bool', oprcom => '&&(anyrange,anyrange)',
   oprcode => 'range_overlaps', oprrest => 'rangesel',
   oprjoin => 'areajoinsel' },
+{ oid => '4552', oid_symbol => 'OID_PERIOD_OVERLAP_OP', descr => 'period overlaps',
+  oprname => '&&&', oprleft => 'anyrange', oprright => 'anyrange',
+  oprresult => 'bool', oprcom => '&&&(anyrange,anyrange)',
+  oprcode => 'period_overlaps', oprrest => 'rangesel',
+  oprjoin => 'areajoinsel' },
 { oid => '3889', oid_symbol => 'OID_RANGE_CONTAINS_ELEM_OP',
   descr => 'contains',
   oprname => '@>', oprleft => 'anyrange', oprright => 'anyelement',
@@ -3314,6 +3319,12 @@
   oprresult => 'bool', oprcom => '&&(anymultirange,anymultirange)',
   oprcode => 'multirange_overlaps_multirange', oprrest => 'multirangesel',
   oprjoin => 'areajoinsel' },
+{ oid => '4553', oid_symbol => 'PERIOD_OID_MULTIRANGE_OVERLAPS_MULTIRANGE_OP',
+  descr => 'overlaps',
+  oprname => '&&&', oprleft => 'anymultirange', oprright => 'anymultirange',
+  oprresult => 'bool', oprcom => '&&&(anymultirange,anymultirange)',
+  oprcode => 'period_multirange_overlaps_multirange', oprrest => 'multirangesel',
+  oprjoin => 'areajoinsel' },
 { oid => '2869', oid_symbol => 'OID_MULTIRANGE_CONTAINS_ELEM_OP',
   descr => 'contains',
   oprname => '@>', oprleft => 'anymultirange', oprright => 'anyelement',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index e4115cd0..a876842f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10506,6 +10506,10 @@
 { oid => '3857',
   proname => 'range_overlaps', prorettype => 'bool',
   proargtypes => 'anyrange anyrange', prosrc => 'range_overlaps' },
+{ oid => '4551',
+  proname => 'period_overlaps', prorettype => 'bool',
+  proargtypes => 'anyrange anyrange', prosrc => 'period_overlaps' },
+
 { oid => '3858',
   proname => 'range_contains_elem', prosupport => 'range_contains_elem_support',
   prorettype => 'bool', proargtypes => 'anyrange anyelement',
@@ -10749,6 +10753,10 @@
   proname => 'multirange_overlaps_multirange', prorettype => 'bool',
   proargtypes => 'anymultirange anymultirange',
   prosrc => 'multirange_overlaps_multirange' },
+{ oid => '4554',
+  proname => 'period_multirange_overlaps_multirange', prorettype => 'bool',
+  proargtypes => 'anymultirange anymultirange',
+  prosrc => 'period_multirange_overlaps_multirange' },
 { oid => '4249',
   proname => 'multirange_contains_elem', prorettype => 'bool',
   proargtypes => 'anymultirange anyelement',
diff --git a/src/include/utils/multirangetypes.h b/src/include/utils/multirangetypes.h
index 84525439..3c6032fa 100644
--- a/src/include/utils/multirangetypes.h
+++ b/src/include/utils/multirangetypes.h
@@ -89,6 +89,10 @@ extern bool range_contains_multirange_internal(TypeCacheEntry *rangetyp,
 extern bool multirange_contains_multirange_internal(TypeCacheEntry *rangetyp,
 													const MultirangeType *mr1,
 													const MultirangeType *mr2);
+extern bool period_overlaps_multirange_internal(TypeCacheEntry *rangetyp,
+											   const RangeType *r,
+											   const MultirangeType *mr);
+
 extern bool range_overlaps_multirange_internal(TypeCacheEntry *rangetyp,
 											   const RangeType *r,
 											   const MultirangeType *mr);
diff --git a/src/include/utils/rangetypes.h b/src/include/utils/rangetypes.h
index 2b574873..7817abf3 100644
--- a/src/include/utils/rangetypes.h
+++ b/src/include/utils/rangetypes.h
@@ -97,6 +97,7 @@ RangeTypePGetDatum(const RangeType *X)
 #define RANGESTRAT_BEFORE				RTLeftStrategyNumber
 #define RANGESTRAT_OVERLEFT				RTOverLeftStrategyNumber
 #define RANGESTRAT_OVERLAPS				RTOverlapStrategyNumber
+#define PERIODSTRAT_OVERLAPS			RTPeriodOverlapStrategyNumber
 #define RANGESTRAT_OVERRIGHT			RTOverRightStrategyNumber
 #define RANGESTRAT_AFTER				RTRightStrategyNumber
 #define RANGESTRAT_ADJACENT				RTSameStrategyNumber
@@ -126,6 +127,8 @@ extern bool range_after_internal(TypeCacheEntry *typcache, const RangeType *r1,
 								 const RangeType *r2);
 extern bool range_adjacent_internal(TypeCacheEntry *typcache, const RangeType *r1,
 									const RangeType *r2);
+extern bool period_overlaps_internal(TypeCacheEntry *typcache, const RangeType *r1,
+									const RangeType *r2);
 extern bool range_overlaps_internal(TypeCacheEntry *typcache, const RangeType *r1,
 									const RangeType *r2);
 extern bool range_overleft_internal(TypeCacheEntry *typcache, const RangeType *r1,
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 7610b011..8217ad0a 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -1147,6 +1147,7 @@ ORDER BY 1, 2;
  #    | #
  &    | &
  &&   | &&
+ &&&  | &&&
  *    | *
  *<   | *>
  *<=  | *>=
@@ -1173,7 +1174,7 @@ ORDER BY 1, 2;
  ~<=~ | ~>=~
  ~<~  | ~>~
  ~=   | ~=
-(29 rows)
+(30 rows)
 
 -- Likewise for negator pairs.
 SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2
@@ -2008,6 +2009,7 @@ ORDER BY 1, 2, 3;
         783 |           28 | <@
         783 |           29 | <^
         783 |           30 | >^
+        783 |           31 | &&&
         783 |           48 | <@
         783 |           68 | <@
        2742 |            1 | &&
@@ -2088,7 +2090,7 @@ ORDER BY 1, 2, 3;
        4000 |           28 | ^@
        4000 |           29 | <^
        4000 |           30 | >^
-(124 rows)
+(125 rows)
 
 -- Check that all opclass search operators have selectivity estimators.
 -- This is not absolutely required, but it seems a reasonable thing
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index 726e9410..ac9cb412 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -289,6 +289,8 @@ DETAIL:  Failing row contains (null, ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:
 INSERT INTO temporal_rng VALUES ('[3,3]', NULL);
 ERROR:  null value in column "valid_at" of relation "temporal_rng" violates not-null constraint
 DETAIL:  Failing row contains ([3,4), null).
+INSERT INTO temporal_rng VALUES ('[3,3]', 'empty');
+ERROR:  period cannot be empty
 --
 -- test a range with both a PK and a UNIQUE constraint
 --
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index c8e8ab99..658fa5fb 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -214,6 +214,8 @@ INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-01', '2018-01-05'));
 INSERT INTO temporal_rng VALUES (NULL, tsrange('2018-01-01', '2018-01-05'));
 INSERT INTO temporal_rng VALUES ('[3,3]', NULL);
 
+INSERT INTO temporal_rng VALUES ('[3,3]', 'empty');
+
 --
 -- test a range with both a PK and a UNIQUE constraint
 --
-- 
2.34.1