v3-0001-Don-t-treat-WITHOUT-OVERLAPS-indexes-as-unique-in.patch

From 4f4428fb41ea79056a13e425826fdac9c7b5d349 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 2 Apr 2024 15:39:04 -0700
Subject: [PATCH v3 1/2] Don't treat WITHOUT OVERLAPS indexes as unique in
 planner

Because the special rangetype 'empty' never overlaps another value, it
is possible for WITHOUT OVERLAPS tables to have two rows with the same
key, despite being indisunique, if the application-time range is
'empty'. So to be safe we should not treat WITHOUT OVERLAPS indexes as
unique in any proofs.

This still needs a test, but I'm having trouble finding a query that
gives wrong results.
---
 src/backend/optimizer/path/indxpath.c     | 5 +++--
 src/backend/optimizer/plan/analyzejoins.c | 6 +++---
 src/backend/optimizer/util/plancat.c      | 1 +
 src/include/nodes/pathnodes.h             | 2 ++
 4 files changed, 9 insertions(+), 5 deletions(-)

diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index c0fcc7d78df..72346f78ebe 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -3498,13 +3498,14 @@ relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel,
 
 		/*
 		 * If the index is not unique, or not immediately enforced, or if it's
-		 * a partial index, it's useless here.  We're unable to make use of
+		 * a partial index, or if it's a WITHOUT OVERLAPS index (so not
+		 * literally unique), it's useless here.  We're unable to make use of
 		 * predOK partial unique indexes due to the fact that
 		 * check_index_predicates() also makes use of join predicates to
 		 * determine if the partial index is usable. Here we need proofs that
 		 * hold true before any joins are evaluated.
 		 */
-		if (!ind->unique || !ind->immediate || ind->indpred != NIL)
+		if (!ind->unique || !ind->immediate || ind->indpred != NIL || ind->hasperiod)
 			continue;
 
 		/*
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index c3fd4a81f8a..dc8327d5769 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -814,8 +814,8 @@ rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel)
 		 * For a plain relation, we only know how to prove uniqueness by
 		 * reference to unique indexes.  Make sure there's at least one
 		 * suitable unique index.  It must be immediately enforced, and not a
-		 * partial index. (Keep these conditions in sync with
-		 * relation_has_unique_index_for!)
+		 * partial index, and not WITHOUT OVERLAPS (Keep these conditions
+		 * in sync with relation_has_unique_index_for!)
 		 */
 		ListCell   *lc;
 
@@ -823,7 +823,7 @@ rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel)
 		{
 			IndexOptInfo *ind = (IndexOptInfo *) lfirst(lc);
 
-			if (ind->unique && ind->immediate && ind->indpred == NIL)
+			if (ind->unique && ind->immediate && ind->indpred == NIL && !ind->hasperiod)
 				return true;
 		}
 	}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 775c3e26cd8..146029577bd 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -457,6 +457,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 			info->predOK = false;	/* set later, in indxpath.c */
 			info->unique = index->indisunique;
 			info->immediate = index->indimmediate;
+			info->hasperiod = index->indisunique && index->indisexclusion;
 			info->hypothetical = false;
 
 			/*
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 14ef296ab72..e24a45f0cd5 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1176,6 +1176,8 @@ struct IndexOptInfo
 	bool		unique;
 	/* is uniqueness enforced immediately? */
 	bool		immediate;
+	/* true if index has WITHOUT OVERLAPS */
+	bool		hasperiod;
 	/* true if index doesn't really exist */
 	bool		hypothetical;
 
-- 
2.42.0