Subject: Create unique GiST indexes

From: Paul A Jungwirth <[email-masked]>
Date: Wed, 31 Dec 2025 14:55:13 -0800

Here is a patch to create unique GiST indexes. It is not really
finished yet, but I thought I would share. It is intended to address
the bug discussed at [1], where calling pg_get_indexdef on a WITHOUT
OVERLAPS constraint generates SQL that Postgres refuses to run. I
realized after sending that email that the thread is on pgsql-bugs,
but I think it belongs on pgsql-hackers now (for one reason, so I can
create a commitfest entry). Please see my notes at that link, and
apologies for the extra noise.

[1] https://www.postgresql.org/message-id/CA%2BrenyU4jKCxrtASJpssZmfrkWhi-%2BQ_PF__jxt8E23T755SPQ%40mail.gmail.com

Yours,

-- 
Paul              ~{:-)
[email-masked]
From cdb24a19d52390b8a3366ca6ba2554215c83c9e7 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email-masked]>
Date: Tue, 4 Nov 2025 19:05:42 -0800
Subject: [PATCH v1] Allow unique GiST indexes

- Added tests showing you can CREATE UNIQUE INDEX ... USING gist, with correct
  semantics building the index and adding to the index.
- Updated the gist README.
- Updated documentation.
- Added excludeFn to the GISTSTATE, used to compare values.
- Added gist_check_unique to do the work. We call this once we have found the
  leaf page, then we check all tuples on the page. As an optimization we could
  first check the consistent function.

Because GiST is lossy (at least if the opclass provides a compress function),
the Datum in the index might not match the Datum in the heap. But the excludeFn
needs heap Datums. So we have to load them before checking. That is not really a
big performance hit though, because we need the heap tuples anyway for
visibility information.

If we detect that we are backing a WITHOUT OVERLAPS constraint (because
indisexclusion is set at the same time as indisunique), then we use overlaps for
the last element's excludeFn. Also we adjust our error messages to match what
the exclusion constraint would produce. This means that almost all the work in
check_exclusion_or_unique_constraint is redundant. Perhaps we should detect that
for WITHOUT OVERLAPS constraints we can rely on the index to have checked that
already? (It still needs to reject empty values though.) In a couple cases I had
to update tests for CREATE CONSTRAINT ... WITHOUT OVERLAPS, because we now
encounter a different conflicting tuple.

I haven't dealt with MVCC issues yet. When we find a not-yet-committed
conflicting tuple, we need to wait and re-check after that transaction has
finished. See nbtree for how this needs to work.

Also I'm trying to handle NULLS NOT DISTINCT correctly. In nbtree the code
assumes that nulls are never equal. So is that handled outside of specific IAMs?
---
 src/backend/access/gist/gist.c                | 359 +++++++++++++++++-
 src/backend/access/gist/gistbuild.c           |  33 +-
 src/backend/utils/sort/tuplesortvariants.c    |   6 +-
 src/include/access/gist_private.h             |   7 +-
 src/include/utils/tuplesort.h                 |   2 +
 src/test/regress/expected/amutils.out         |   4 +-
 src/test/regress/expected/gist.out            |  32 ++
 .../regress/expected/without_overlaps.out     |   4 +-
 src/test/regress/sql/gist.sql                 |  17 +
 9 files changed, 447 insertions(+), 17 deletions(-)

diff --git a/src/backend/access/gist/gist.c b/src/backend/access/gist/gist.c
index 4b943b7f43e..14abfc16b6a 100644
--- a/src/backend/access/gist/gist.c
+++ b/src/backend/access/gist/gist.c
@@ -16,14 +16,18 @@
 
 #include "access/gist_private.h"
 #include "access/gistscan.h"
+#include "access/tableam.h"
 #include "access/xloginsert.h"
+#include "catalog/pg_am.h"
 #include "catalog/pg_collation.h"
+#include "commands/defrem.h"
 #include "commands/vacuum.h"
 #include "miscadmin.h"
 #include "nodes/execnodes.h"
 #include "storage/predicate.h"
 #include "utils/fmgrprotos.h"
 #include "utils/index_selfuncs.h"
+#include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/rel.h"
 
@@ -69,7 +73,7 @@ gisthandler(PG_FUNCTION_ARGS)
 		.amconsistentequality = false,
 		.amconsistentordering = false,
 		.amcanbackward = false,
-		.amcanunique = false,
+		.amcanunique = true,
 		.amcanmulticol = true,
 		.amoptionalkey = true,
 		.amsearcharray = false,
@@ -161,6 +165,7 @@ gistbuildempty(Relation index)
  *
  *	  This is the public interface routine for tuple insertion in GiSTs.
  *	  It doesn't do any work; just locks the relation and passes the buck.
+ *	  TODO: I don't see where it locks the relation?
  */
 bool
 gistinsert(Relation r, Datum *values, bool *isnull,
@@ -172,12 +177,15 @@ gistinsert(Relation r, Datum *values, bool *isnull,
 	GISTSTATE  *giststate = (GISTSTATE *) indexInfo->ii_AmCache;
 	IndexTuple	itup;
 	MemoryContext oldCxt;
+	bool		known_unique;
 
 	/* Initialize GISTSTATE cache if first call in this statement */
 	if (giststate == NULL)
 	{
 		oldCxt = MemoryContextSwitchTo(indexInfo->ii_Context);
 		giststate = initGISTstate(r);
+		if (checkUnique != UNIQUE_CHECK_NO)
+			initGISTstateExclude(giststate, r);
 		giststate->tempCxt = createTempGistContext();
 		indexInfo->ii_AmCache = giststate;
 		MemoryContextSwitchTo(oldCxt);
@@ -188,13 +196,13 @@ gistinsert(Relation r, Datum *values, bool *isnull,
 	itup = gistFormTuple(giststate, r, values, isnull, true);
 	itup->t_tid = *ht_ctid;
 
-	gistdoinsert(r, itup, 0, giststate, heapRel, false);
+	known_unique = gistdoinsert(r, itup, checkUnique, values, isnull, 0, giststate, heapRel, false);
 
 	/* cleanup */
 	MemoryContextSwitchTo(oldCxt);
 	MemoryContextReset(giststate->tempCxt);
 
-	return false;
+	return known_unique;
 }

@@ -630,14 +638,232 @@ gistplacetopage(Relation rel, Size freespace, GISTSTATE *giststate,
 	return is_split;
 }
 
+/*
+ * gist_check_unique -- enforce UNIQUEness with given excludeFn.
+ *
+ * ereports if there is a definite conflict, or returns InvalidTransactionId if
+ * there is no conflict, or returns the TransactionId that must be waited on if
+ * there is a potential conflict from a not-yet-committed transaction.
+ *
+ * The giststate must already point to a leaf page.
+ *
+ * Our definition of uniqueness is based on the excludeFn, which does not
+ * necessarily check for equality. That lets you create so-called UNIQUE indexes
+ * that forbid something else, like overlaps. This is useful for indexes
+ * backing temporal constraints (i.e. WITHOUT OVERLAPS).
+ *
+ * Uniqueness can only be enforced if the GISTENTRY Datum is the same as the
+ * heap Datum---in other words there is no compress support proc.
+ */
+static TransactionId
+gist_check_unique(Relation rel, GISTSTATE *giststate, GISTInsertState *state,
+				  Relation heapRel, IndexTuple itup, Datum *newvals, bool *newnulls,
+				  IndexUniqueCheck checkUnique, bool *is_unique)
+{
+	Page		page;
+	OffsetNumber maxoff;
+	OffsetNumber i;
+	MemoryContext oldcxt;
+	bool		conflicts = false;
+	SnapshotData SnapshotDirty;
+	IndexFetchTableData *scan;
+	TupleTableSlot *slot;
+	bool		call_again = false;
+	bool		found = false;
+	bool		all_dead = false; // TODO: use this? nbtree does.
+	Datum		oldvals[INDEX_MAX_KEYS];
+	bool		oldnulls[INDEX_MAX_KEYS];
+
+	InitDirtySnapshot(SnapshotDirty);
+
+	slot = table_slot_create(heapRel, NULL);
+
+	/* Check all tuples on the page for a conflict. */
+	page = state->stack->page;
+	maxoff = PageGetMaxOffsetNumber(page);
+	for (i = FirstOffsetNumber; i <= maxoff && !conflicts; i = OffsetNumberNext(i))
+	{
+		ItemId		iid = PageGetItemId(page, i);
+		IndexTuple	it;
+		// bool		match;
+		// bool		recheck;	// TODO
+		// bool		recheck_distances;
+		int			j;
+
+		/*
+		 * XXX: orindary scans only skip dead items if passed
+		 * ignore_killed_tuples. Do we ever want to see killed tuples?
+		 */
+		if (ItemIdIsDead(iid))
+			continue;
+
+		it = (IndexTuple) PageGetItem(page, iid);
+
+		/*
+		 * Get the original Datums from the heap.
+		 * We have to look there anyway for visibility info.
+		 * We'll pass these Datums to the excludeFn.
+		 * If the opclass has no compression function,
+		 * we could use the Datums from the index tuple instead,
+		 * but as long as we're loading the heap record
+		 * we might as well use it.
+		 */
+
+		scan = table_index_fetch_begin(heapRel);
+		do {
+			// TODO: What if t_tid gets modified?
+			found |= table_index_fetch_tuple(scan, &it->t_tid, &SnapshotDirty, slot,
+											&call_again, &all_dead);
+			/*
+			 * XXX: Is looping on call_again actually needed here?
+			 * table_index_fetch_tuple_check ignores it.
+			 * But since we use the Datums, we do need the latest values.
+			 */
+		} while (call_again);
+		if (!found)
+		{
+			table_index_fetch_end(scan);
+			continue;
+		}
+		slot_getallattrs(slot);
+
+		conflicts = true;
+
+		oldcxt = MemoryContextSwitchTo(giststate->tempCxt);
+
+		/* Check all the key elements against excludeFn */
+		for (j = 0; j < giststate->leafTupdesc->natts; j++)
+		{
+			int		heapattr;
+			Datum	oldval;
+			Datum	newval;
+			bool	oldIsNull;
+			Datum	test;
+
+			Assert(OidIsValid(giststate->excludeFn[j].fn_oid));
+
+			// TODO: If it's an expression index with a compress function,
+			// we have to give up. Unless we want to re-evaluate the expression.
+			heapattr = rel->rd_index->indkey.values[j];
+
+			// XXX: I assume the index has a null iff the heap does?
+			// XXX: What about nulls_not_distinct? Btree seems to ignore that
+			// though in access/nbtree/nbtinsert.c:_bt_doinsert
+			oldIsNull = slot->tts_isnull[heapattr - 1];
+			if (oldIsNull)
+			{
+				conflicts = false;
+				break;
+			}
+			else
+			{
+				oldval = slot->tts_values[heapattr - 1];
+			}
+			/*
+			 * Record the heap Datums in index-attribute order,
+			 * so we can build an error message below.
+			 */
+			oldnulls[j] = oldIsNull;
+			oldvals[j] = oldval;
+
+			// XXX: I assume the index has a null iff the heap does?
+			// XXX: What about nulls_not_distinct? Btree seems to ignore that
+			// though in access/nbtree/nbtinsert.c:_bt_doinsert
+			if (newnulls[j])
+			{
+				// XXX: probably a higher level prevents even checking the index here?
+				conflicts = false;
+				break;
+			}
+			else
+			{
+				// TODO: detoast the newvals up front, only once?
+				newval = newvals[j];
+			}
+
+			// TODO: If we check nulls_not_distinct here,
+			// and both new and old are null,
+			// then treat it as a conflict.
+			// Somehow nbtree doesn't do that.
+
+			test = FunctionCall2Coll(&giststate->excludeFn[j],
+									 InvalidOid,	// TODO: collation
+									 oldval,
+									 newval);
+			/*
+			 * If all attributes conflict, then we violate uniqueness.
+			 * So we can abort on the first non-conflicting attribute.
+			 */
+			conflicts &= DatumGetBool(test);
+			if (!conflicts)
+				break;
+		}
+
+		table_index_fetch_end(scan);
+		MemoryContextSwitchTo(oldcxt);
+	}
+	*is_unique = !conflicts;
+
+	// TODO: depends on checkUnique
+	// TODO: deal with MVCC
+	if (conflicts) {
+		char *key_desc = BuildIndexValueDescription(rel, newvals, newnulls);
+
+		/* For WITHOUT OVERLAPS, match the exclusion constraint message */
+		if (rel->rd_index->indisexclusion)
+		{
+			char *old_key_desc = BuildIndexValueDescription(rel, oldvals, oldnulls);
+			if (state->is_build)
+				ereport(ERROR,
+						(errcode(ERRCODE_UNIQUE_VIOLATION),
+						 errmsg("could not create exclusion constraint \"%s\"",
+								RelationGetRelationName(rel)),
+						 key_desc && old_key_desc ? errdetail("Key %s conflicts with key %s.",
+											  key_desc, old_key_desc) : 0,
+						 errtableconstraint(heapRel,
+											RelationGetRelationName(rel))));
+			else
+				ereport(ERROR,
+						(errcode(ERRCODE_UNIQUE_VIOLATION),
+						 errmsg("conflicting key value violates exclusion constraint \"%s\"",
+								RelationGetRelationName(rel)),
+						 key_desc && old_key_desc ? errdetail("Key %s conflicts with existing key %s.",
+											  key_desc, old_key_desc) : 0,
+						 errtableconstraint(heapRel,
+											RelationGetRelationName(rel))));
+		}
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_UNIQUE_VIOLATION),
+					 errmsg("duplicate key value violates unique constraint \"%s\"",
+							RelationGetRelationName(rel)),
+					 key_desc ? errdetail("Key %s already exists.",
+										  key_desc) : 0,
+					 errtableconstraint(heapRel,
+										RelationGetRelationName(rel))));
+	}
+
+	ExecDropSingleTupleTableSlot(slot);
+
+	// TODO: xwait when it is not unique
+	return InvalidTransactionId;
+}
+
 /*
  * Workhorse routine for doing insertion into a GiST index. Note that
  * this routine assumes it is invoked in a short-lived memory context,
  * so it does not bother releasing palloc'd allocations.
+ *
+ * The result value is only significant for UNIQUE_CHECK_PARTIAL:
+ * it must be true if the entry is known unique, else false.
+ * (In the current implementation we'll also return true after a
+ * successful UNIQUE_CHECK_YES or UNIQUE_CHECK_EXISTING call, but
+ * that's just a coding artifact.)
  */
-void
-gistdoinsert(Relation r, IndexTuple itup, Size freespace,
-			 GISTSTATE *giststate, Relation heapRel, bool is_build)
+bool
+gistdoinsert(Relation r, IndexTuple itup, IndexUniqueCheck checkUnique,
+			 Datum *values, bool *isnull,
+			 Size freespace, GISTSTATE *giststate, Relation heapRel, bool is_build)
 {
 	ItemId		iid;
 	IndexTuple	idxtuple;
@@ -645,6 +871,10 @@ gistdoinsert(Relation r, IndexTuple itup, Size freespace,
 	GISTInsertStack *stack;
 	GISTInsertState state;
 	bool		xlocked = false;
+	bool		checkingunique = (checkUnique != UNIQUE_CHECK_NO);
+	bool		is_unique = false;
+
+search:
 
 	memset(&state, 0, sizeof(GISTInsertState));
 	state.freespace = freespace;
@@ -836,6 +1066,12 @@ gistdoinsert(Relation r, IndexTuple itup, Size freespace,
 			 * Leaf page. Insert the new key. We've already updated all the
 			 * parents on the way down, but we might have to split the page if
 			 * it doesn't fit. gistinserttuple() will take care of that.
+			 * TODO: If the parents are already updated,
+			 * isn't that a problem if the uniqueness check fails??
+			 * Do we need to descend twice and keep the locks longer for unique
+			 * indexes?
+			 * If guess if the new entry *is* non-unique, the parents didn't
+			 * actually change, right?
 			 */
 
 			/*
@@ -889,16 +1125,47 @@ gistdoinsert(Relation r, IndexTuple itup, Size freespace,
 
 			/* now state.stack->(page, buffer and blkno) points to leaf page */
 
-			gistinserttuple(&state, stack, giststate, itup,
-							InvalidOffsetNumber);
+			// TODO: now that we have the lock,
+			// we can check for uniqueness and raise an error if needed.
+			if (checkingunique)
+			{
+				TransactionId xwait;
+				// If there are null values, it can't be unique
+				// TODO: but what about NULLS NOT DISTINCT? I don't see that in
+				// nbtree _bt_doinsert.
+				// For btree this seems to be handled in utils/sort/tuplesortvariants.c
+				// (which of course is outside of access/nbtree, but has btree
+				// in many function names).
+
+				xwait = gist_check_unique(r, giststate, &state, heapRel, itup,
+										  values, isnull,
+										  checkUnique, &is_unique);
+				if (unlikely(TransactionIdIsValid(xwait)))
+				{
+					// TODO: more here
+					goto search;
+				}
+			}
+
+			if (checkUnique != UNIQUE_CHECK_EXISTING)
+			{
+				// TODO: need SERIALIZABLE check like nbtree?
+
+				gistinserttuple(&state, stack, giststate, itup,
+								InvalidOffsetNumber);
+			}
+
 			LockBuffer(stack->buffer, GIST_UNLOCK);
 
 			/* Release any pins we might still hold before exiting */
 			for (; stack; stack = stack->parent)
 				ReleaseBuffer(stack->buffer);
+
 			break;
 		}
 	}
+
+	return is_unique;
 }
 
 /*
@@ -1623,6 +1890,15 @@ initGISTstate(Relation index)
 		else
 			giststate->fetchFn[i].fn_oid = InvalidOid;
 
+		/*
+		 * We only need excludeFn for UNIQUE indexes.
+		 * XXX: We could use this to enforce exclusion constraints
+		 * from the index instead of the constraint (which is how
+		 * unique constraints work at any rate). Or we could at least
+		 * enforce WITHOUT OVERLAPS semantics.
+		 */
+		giststate->excludeFn[i].fn_oid = InvalidOid;
+
 		/*
 		 * If the index column has a specified collation, we should honor that
 		 * while doing comparisons.  However, we may have a collatable storage
@@ -1652,6 +1928,7 @@ initGISTstate(Relation index)
 		giststate->equalFn[i].fn_oid = InvalidOid;
 		giststate->distanceFn[i].fn_oid = InvalidOid;
 		giststate->fetchFn[i].fn_oid = InvalidOid;
+		giststate->excludeFn[i].fn_oid = InvalidOid;
 		giststate->supportCollation[i] = InvalidOid;
 	}
 
@@ -1660,6 +1937,72 @@ initGISTstate(Relation index)
 	return giststate;
 }
 
+/*
+ * Initialize excludeFn (assumes this is a UNIQUE index).
+ */
+void
+initGISTstateExclude(GISTSTATE *giststate, Relation index)
+{
+	int			natts = IndexRelationGetNumberOfKeyAttributes(index);
+	int			i;
+
+	Assert(index->rd_index->indisunique);
+
+	for (i = 0; i < natts; i++)
+	{
+		/*
+		 * Set the operator (or rather its function) for the equality CompareType
+		 * (or fail).
+		 *
+		 * Even the GiST AM doesn't know the strategy numbers chosen by GiST
+		 * opfamilies. For example btree_gist uses BT*StrategyNumber even though
+		 * built-in opclasses uses RT*StrategyNumber. So we use the CompareType
+		 * infrastructure to find an operator that implements equality.
+		 *
+		 * XXX: We assume that all opclasses within an opfamily use the same
+		 * strategy numbers. This is probably a fair assumption, but it
+		 * means we disregard the index attributes' opclasses when getting
+		 * stratnums.
+		 */
+		RegProcedure proc;
+		Oid opfamily = index->rd_opfamily[i];
+		CompareType cmptype;
+		StrategyNumber strat;
+		Oid				opid;
+
+		/*
+		 * If this is a WITHOUT OVERLAPS index, we use overlaps for the last
+		 * element. Otherwise we use equality. The only time both indisunique
+		 * and indisexclusion are set is for WITHOUT OVERLAPS.
+		 *
+		 * XXX: We could do checking for all exclusion constraints here,
+		 * if we wanted to look up their operators.
+		 */
+		if (index->rd_index->indisexclusion && i == natts - 1)
+			cmptype = COMPARE_OVERLAP;
+		else
+			cmptype = COMPARE_EQ;
+
+		strat = IndexAmTranslateCompareType(cmptype, GIST_AM_OID, opfamily, false);
+
+
+		opid = get_opfamily_member(index->rd_opfamily[i],
+								   index->rd_opcintype[i],
+								   index->rd_opcintype[i], strat);
+		if (!OidIsValid(opid))
+			ereport(ERROR,
+					errmsg("could not identify equality operator for unique constraint"),
+					errdetail("Could not translate compare type %d for operator family \"%s\" of access method \"%s\".",
+							  cmptype, get_opfamily_name(opfamily, false), get_am_name(GIST_AM_OID)));
+
+		proc = get_opcode(opid);
+		if (!OidIsValid(proc))
+			elog(ERROR, "cache lookup failed for operator %u", opid);
+
+		fmgr_info_cxt(proc, &(giststate->excludeFn[i]), giststate->scanCxt);
+	}
+}
+
 void
 freeGISTstate(GISTSTATE *giststate)
 {
diff --git a/src/backend/access/gist/gistbuild.c b/src/backend/access/gist/gistbuild.c
index b9fa196149d..9986af49c61 100644
--- a/src/backend/access/gist/gistbuild.c
+++ b/src/backend/access/gist/gistbuild.c
@@ -108,6 +108,9 @@ typedef struct
 	BlockNumber pages_allocated;
 
 	BulkWriteState *bulkstate;
+
+	bool		isunique;
+	bool		nulls_not_distinct;
 } GISTBuildState;
 
 #define GIST_SORTED_BUILD_PAGE_NUM 4
@@ -198,6 +201,10 @@ gistbuild(Relation heap, Relation index, IndexInfo *indexInfo)
 	buildstate.heaprel = heap;
 	buildstate.sortstate = NULL;
 	buildstate.giststate = initGISTstate(index);
+	if (indexInfo->ii_Unique)
+		initGISTstateExclude(buildstate.giststate, index);
+	buildstate.isunique = indexInfo->ii_Unique;
+	buildstate.nulls_not_distinct = indexInfo->ii_NullsNotDistinct;
 
 	/*
 	 * Create a temporary memory context that is reset once for each tuple
@@ -266,6 +273,8 @@ gistbuild(Relation heap, Relation index, IndexInfo *indexInfo)
 		 */
 		buildstate.sortstate = tuplesort_begin_index_gist(heap,
 														  index,
+														  buildstate.isunique,
+														  buildstate.nulls_not_distinct,
 														  maintenance_work_mem,
 														  NULL,
 														  TUPLESORT_NONE);
@@ -862,8 +871,28 @@ gistBuildCallback(Relation index,
 		 * There's no buffers (yet). Since we already have the index relation
 		 * locked, we call gistdoinsert directly.
 		 */
-		gistdoinsert(index, itup, buildstate->freespace,
-					 buildstate->giststate, buildstate->heaprel, true);
+		bool known_unique = gistdoinsert(index, itup,
+										 buildstate->isunique ? UNIQUE_CHECK_YES
+															  : UNIQUE_CHECK_NO,
+										 values, isnull,
+										 buildstate->freespace,
+										 buildstate->giststate,
+										 buildstate->heaprel, true);
+		/*
+		 * There are no other users of the index yet, so if we aren't sure it's
+		 * unique, there must be duplicates.
+		 * TODO: right??
+		 */
+		// TODO: say which keys are duplicated if possible (as in
+		// utils/sort/tuplesortvariants.c)
+		if (buildstate->isunique && !known_unique)
+			ereport(ERROR,
+					(errcode(ERRCODE_UNIQUE_VIOLATION),
+					 errmsg("could not create unique index \"%s\"",
+							RelationGetRelationName(buildstate->indexrel)),
+					 errdetail("Duplicate keys exist."),
+					 errtableconstraint(buildstate->heaprel,
+										RelationGetRelationName(buildstate->indexrel))));
 	}
 
 	MemoryContextSwitchTo(oldCtx);
diff --git a/src/backend/utils/sort/tuplesortvariants.c b/src/backend/utils/sort/tuplesortvariants.c
index a1f5c19ee97..8d2629490bd 100644
--- a/src/backend/utils/sort/tuplesortvariants.c
+++ b/src/backend/utils/sort/tuplesortvariants.c
@@ -490,6 +490,8 @@ tuplesort_begin_index_hash(Relation heapRel,
 Tuplesortstate *
 tuplesort_begin_index_gist(Relation heapRel,
 						   Relation indexRel,
+						   bool enforceUnique,
+						   bool uniqueNullsNotDistinct,
 						   int workMem,
 						   SortCoordinate coordinate,
 						   int sortopt)
@@ -521,8 +523,8 @@ tuplesort_begin_index_gist(Relation heapRel,
 
 	arg->index.heapRel = heapRel;
 	arg->index.indexRel = indexRel;
-	arg->enforceUnique = false;
-	arg->uniqueNullsNotDistinct = false;
+	arg->enforceUnique = enforceUnique;
+	arg->uniqueNullsNotDistinct = uniqueNullsNotDistinct;
 
 	/* Prepare SortSupport data for each column */
 	base->sortKeys = (SortSupport) palloc0(base->nKeys *
diff --git a/src/include/access/gist_private.h b/src/include/access/gist_private.h
index 39404ec7cdb..415d1d3b00a 100644
--- a/src/include/access/gist_private.h
+++ b/src/include/access/gist_private.h
@@ -92,6 +92,7 @@ typedef struct GISTSTATE
 	FmgrInfo	equalFn[INDEX_MAX_KEYS];
 	FmgrInfo	distanceFn[INDEX_MAX_KEYS];
 	FmgrInfo	fetchFn[INDEX_MAX_KEYS];
+	FmgrInfo	excludeFn[INDEX_MAX_KEYS];
 
 	/* Collations to pass to the support functions */
 	Oid			supportCollation[INDEX_MAX_KEYS];
@@ -407,9 +408,13 @@ extern bool gistinsert(Relation r, Datum *values, bool *isnull,
 					   struct IndexInfo *indexInfo);
 extern MemoryContext createTempGistContext(void);
 extern GISTSTATE *initGISTstate(Relation index);
+extern void initGISTstateExclude(GISTSTATE *giststate, Relation index);
 extern void freeGISTstate(GISTSTATE *giststate);
-extern void gistdoinsert(Relation r,
+extern bool gistdoinsert(Relation r,
 						 IndexTuple itup,
+						 IndexUniqueCheck checkUnique,
+						 Datum *values,
+						 bool *isnull,
 						 Size freespace,
 						 GISTSTATE *giststate,
 						 Relation heapRel,
diff --git a/src/include/utils/tuplesort.h b/src/include/utils/tuplesort.h
index 63a7cc13a31..b9f04f6dc50 100644
--- a/src/include/utils/tuplesort.h
+++ b/src/include/utils/tuplesort.h
@@ -440,6 +440,8 @@ extern Tuplesortstate *tuplesort_begin_index_hash(Relation heapRel,
 												  int sortopt);
 extern Tuplesortstate *tuplesort_begin_index_gist(Relation heapRel,
 												  Relation indexRel,
+												  bool enforceUnique,
+												  bool uniqueNullsNotDistinct,
 												  int workMem, SortCoordinate coordinate,
 												  int sortopt);
 extern Tuplesortstate *tuplesort_begin_index_brin(int workMem, SortCoordinate coordinate,
diff --git a/src/test/regress/expected/amutils.out b/src/test/regress/expected/amutils.out
index 7ab6113c619..9069e082ca5 100644
--- a/src/test/regress/expected/amutils.out
+++ b/src/test/regress/expected/amutils.out
@@ -72,7 +72,7 @@ select prop,
  bitmap_scan        |    | t     | 
  backward_scan      |    | f     | 
  can_order          | f  |       | 
- can_unique         | f  |       | 
+ can_unique         | t  |       | 
  can_multi_col      | t  |       | 
  can_exclude        | t  |       | 
  can_include        | t  |       | 
@@ -156,7 +156,7 @@ select amname, prop, pg_indexam_has_property(a.oid, prop) as p
  gin    | can_include   | f
  gin    | bogus         | 
  gist   | can_order     | f
- gist   | can_unique    | f
+ gist   | can_unique    | t
  gist   | can_multi_col | t
  gist   | can_exclude   | t
  gist   | can_include   | t
diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out
index c75bbb23b6e..60e5eb7c407 100644
--- a/src/test/regress/expected/gist.out
+++ b/src/test/regress/expected/gist.out
@@ -390,6 +390,38 @@ ERROR:  lossy distance functions are not supported in index-only scans
 -- Force an index build using buffering.
 create index gist_tbl_box_index_forcing_buffering on gist_tbl using gist (p)
   with (buffering=on, fillfactor=50);
+-- create unique indexes
+create table gist_rngtbl (id int4range);
+insert into gist_rngtbl values ('[1,2)'), ('[2,3)'); -- okay
+create unique index uq_gist_rngtbl on gist_rngtbl using gist (id);
+insert into gist_rngtbl values ('[3,4)'), ('[4,5)'); -- okay
+\d gist_rngtbl
+             Table "public.gist_rngtbl"
+ Column |   Type    | Collation | Nullable | Default 
+--------+-----------+-----------+----------+---------
+ id     | int4range |           |          | 
+Indexes:
+    "uq_gist_rngtbl" UNIQUE, gist (id)
+
+select pg_get_indexdef('uq_gist_rngtbl'::regclass);
+                             pg_get_indexdef                              
+--------------------------------------------------------------------------
+ CREATE UNIQUE INDEX uq_gist_rngtbl ON public.gist_rngtbl USING gist (id)
+(1 row)
+
+-- enforced on build
+drop index uq_gist_rngtbl;
+insert into gist_rngtbl values ('[1,2)');
+create unique index uq_gist_rngtbl on gist_rngtbl using gist (id); -- fail
+ERROR:  could not create unique index "uq_gist_rngtbl"
+DETAIL:  Key (id)=([1,2)) is duplicated.
+truncate gist_rngtbl;
+-- enforced on insert
+create unique index uq_gist_rngtbl on gist_rngtbl using gist (id);
+insert into gist_rngtbl values ('[1,2)'), ('[2,3)'); -- okay
+insert into gist_rngtbl values ('[1,2)'); -- fail
+ERROR:  duplicate key value violates unique constraint "uq_gist_rngtbl"
+DETAIL:  Key (id)=([1,2)) already exists.
 -- Clean up
 reset enable_seqscan;
 reset enable_bitmapscan;
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index f3144bdc39c..ef44ec6b51e 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -641,7 +641,7 @@ BEGIN;
   INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05')));
   ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
 ERROR:  could not create exclusion constraint "temporal_mltrng_pk"
-DETAIL:  Key (id, valid_at)=([1,2), {[2018-01-02,2018-02-03)}) conflicts with key (id, valid_at)=([1,2), {[2018-01-01,2018-01-05)}).
+DETAIL:  Key (id, valid_at)=([1,2), {[2018-01-01,2018-01-05)}) conflicts with key (id, valid_at)=([1,2), {[2018-01-02,2018-02-03)}).
 ROLLBACK;
 -- rejects empty:
 BEGIN;
@@ -766,7 +766,7 @@ BEGIN;
   INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05')));
   ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS);
 ERROR:  could not create exclusion constraint "temporal_mltrng3_uq"
-DETAIL:  Key (id, valid_at)=([1,2), {[2018-01-02,2018-02-03)}) conflicts with key (id, valid_at)=([1,2), {[2018-01-01,2018-01-05)}).
+DETAIL:  Key (id, valid_at)=([1,2), {[2018-01-01,2018-01-05)}) conflicts with key (id, valid_at)=([1,2), {[2018-01-02,2018-02-03)}).
 ROLLBACK;
 -- rejects empty:
 BEGIN;
diff --git a/src/test/regress/sql/gist.sql b/src/test/regress/sql/gist.sql
index 6f1fc65f128..c83c6f3c741 100644
--- a/src/test/regress/sql/gist.sql
+++ b/src/test/regress/sql/gist.sql
@@ -173,6 +173,23 @@ select p from gist_tbl order by circle(p,1) <-> point(0,0) limit 1;
 create index gist_tbl_box_index_forcing_buffering on gist_tbl using gist (p)
   with (buffering=on, fillfactor=50);
 
+-- create unique indexes
+create table gist_rngtbl (id int4range);
+insert into gist_rngtbl values ('[1,2)'), ('[2,3)'); -- okay
+create unique index uq_gist_rngtbl on gist_rngtbl using gist (id);
+insert into gist_rngtbl values ('[3,4)'), ('[4,5)'); -- okay
+\d gist_rngtbl
+select pg_get_indexdef('uq_gist_rngtbl'::regclass);
+-- enforced on build
+drop index uq_gist_rngtbl;
+insert into gist_rngtbl values ('[1,2)');
+create unique index uq_gist_rngtbl on gist_rngtbl using gist (id); -- fail
+truncate gist_rngtbl;
+-- enforced on insert
+create unique index uq_gist_rngtbl on gist_rngtbl using gist (id);
+insert into gist_rngtbl values ('[1,2)'), ('[2,3)'); -- okay
+insert into gist_rngtbl values ('[1,2)'); -- fail
+
 -- Clean up
 reset enable_seqscan;
 reset enable_bitmapscan;
-- 
2.47.3

---

From: Paul A Jungwirth <[email-masked]>
Date: Fri, 13 Mar 2026 13:29:38 -0700

On Fri, Jan 2, 2026 at 10:39 AM Paul A Jungwirth
<[email-masked]> wrote:
>
> This is the missing MVCC functionality I mentioned when I posted the
> patch. It's the next thing on my list to work on. As I said the patch
> is not really done. But it took longer than I expected to send a reply
> to Matthias, and I wanted to post something before the commitfest
> deadline. And I thought I at least had enough to get feedback on the
> overall approach.
>
> I think your example here would make a great isolation test though.
> I'll incorporate that into future work, or please feel free to write
> it yourself and share if you like.

I realized my approach to this patch is just wrong, because I'm only
searching one index leaf page for conflicts (whichever one the insert
lands on), and they might be in different pages, due to changes in
penalty, splits, and maybe other reasons. I've been working on a
different approach, but I don't have something ready to send in yet.
I've updated the commitfest status to Waiting on Author.

Yours,