pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
16+ messages / 6 participants
[nested] [flat]
* BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-10-29 09:54 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 16+ messages in thread
From: PG Bug reporting form @ 2025-10-29 09:54 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: n.bartek3762@gmail.com
The following bug has been logged on the website:
Bug reference: 19098
Logged by: Bartłomiej Nowak
Email address: n.bartek3762@gmail.com
PostgreSQL version: 18.0
Operating system: Linux pop-os x86_64
Description:
Using unique constraint, at Postgres 18, I am able to use `WITHOUT OVERLAPS`
using that, in fact I created index, that I can see at pg_indexes:
`CREATE UNIQUE INDEX inventoryoffer_unique_index ON public.test_table USING
gist (inventory_id, account_id, from_offer_id, for_account_id,
_availability_range);`
It says, that it is gist UNIQUE index.
But if I would like to just create index like that (copy and paste that
definition from pg_indexes)
I am getting error:
[0A000] ERROR: access method "gist" does not support unique indexes
which sounds weird, in fact of `WITHOUT OVERLAPS` existence
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-10-29 11:31 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 16+ messages in thread
From: Laurenz Albe @ 2025-10-29 11:31 UTC (permalink / raw)
To: n.bartek3762@gmail.com; pgsql-bugs@lists.postgresql.org
On Wed, 2025-10-29 at 09:54 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 18.0
>
> Using unique constraint, at Postgres 18, I am able to use `WITHOUT OVERLAPS`
> using that, in fact I created index, that I can see at pg_indexes:
> `CREATE UNIQUE INDEX inventoryoffer_unique_index ON public.test_table USING
> gist (inventory_id, account_id, from_offer_id, for_account_id,
> _availability_range);`
> It says, that it is gist UNIQUE index.
> But if I would like to just create index like that (copy and paste that
> definition from pg_indexes)
> I am getting error:
> [0A000] ERROR: access method "gist" does not support unique indexes
> which sounds weird, in fact of `WITHOUT OVERLAPS` existence
I think I see what you mean:
CREATE TABLE temp (
id bigint NOT NULL,
valid tstzrange NOT NULL,
CONSTRAINT temp_pkey PRIMARY KEY (id, valid WITHOUT OVERLAPS)
);
SELECT pg_get_indexdef('temp_pkey'::regclass);
pg_get_indexdef
----------------------------------------------------------------------
CREATE UNIQUE INDEX temp_pkey ON laurenz.temp USING gist (id, valid)
That CREATE INDEX statement won't work.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-10-29 12:43 Kirill Reshke <reshkekirill@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 16+ messages in thread
From: Kirill Reshke @ 2025-10-29 12:43 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: n.bartek3762@gmail.com; pgsql-bugs@lists.postgresql.org
On Wed, 29 Oct 2025 at 16:31, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
>
> I think I see what you mean:
>
> CREATE TABLE temp (
> id bigint NOT NULL,
> valid tstzrange NOT NULL,
> CONSTRAINT temp_pkey PRIMARY KEY (id, valid WITHOUT OVERLAPS)
> );
>
> SELECT pg_get_indexdef('temp_pkey'::regclass);
>
> pg_get_indexdef
> ----------------------------------------------------------------------
> CREATE UNIQUE INDEX temp_pkey ON laurenz.temp USING gist (id, valid)
>
> That CREATE INDEX statement won't work.
>
> Yours,
> Laurenz Albe
>
>
Yep, this is probably a valid rewording of the first email. Is it a
problem that is worth fixing?
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-10-29 13:52 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Kirill Reshke <reshkekirill@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Laurenz Albe @ 2025-10-29 13:52 UTC (permalink / raw)
To: Kirill Reshke <reshkekirill@gmail.com>; +Cc: n.bartek3762@gmail.com; pgsql-bugs@lists.postgresql.org
On Wed, 2025-10-29 at 17:43 +0500, Kirill Reshke wrote:
> On Wed, 29 Oct 2025 at 16:31, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> >
> >
> > I think I see what you mean:
> >
> > CREATE TABLE temp (
> > id bigint NOT NULL,
> > valid tstzrange NOT NULL,
> > CONSTRAINT temp_pkey PRIMARY KEY (id, valid WITHOUT OVERLAPS)
> > );
> >
> > SELECT pg_get_indexdef('temp_pkey'::regclass);
> >
> > pg_get_indexdef
> > ----------------------------------------------------------------------
> > CREATE UNIQUE INDEX temp_pkey ON laurenz.temp USING gist (id, valid)
> >
> > That CREATE INDEX statement won't work.
> >
> > Yours,
> > Laurenz Albe
> >
> >
>
> Yep, this is probably a valid rewording of the first email. Is it a
> problem that is worth fixing?
I personally think so, although the fact that this occurs in the regression tests
might mean that it is intentional.
Anyway, here is a patch that fixes the problem for me. I am not happy with it,
because it hard-codes that CREATE UNIQUE INDEX only works with B-tree indexes,
but I don't have a better idea.
Yours,
Laurenz Albe
Attachments:
[text/x-patch] v1-0001-Fix-pg_get_indexdef-for-temporal-constraints.patch (7.3K, ../../2b831e6818fb193e4826da7d087816cdf3e56c98.camel@cybertec.at/2-v1-0001-Fix-pg_get_indexdef-for-temporal-constraints.patch)
download | inline diff:
From 501de87eea0552d4693b09ff2d46059e83e6f872 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Wed, 29 Oct 2025 14:49:49 +0100
Subject: [PATCH v1] Fix pg_get_indexdef for temporal constraints
The indexes created by such constraints are marked as unique in pg_index,
so pg_get_indexdef() produced a CREATE UNIQUE INDEX statement, which failed
for anything but B-tree indexes.
To fix, suppress the UNIQUE in the output for all except B-tree indexes.
This is hacky, since it hard-codes that CREATE UNIQUE INDEX works only
for B-tree indexes, which may change in the future. Perhaps it would have
ben better not to set "indisunique" for such indexes, but it is too late
to change that.
Author: Laurenz Albe <laurenz.albe@cybertec.at>
Discussion: https://postgr.es/m/19098-7f8deb5093c13da2%40postgresql.org
Backpatch-To: 18
---
.../btree_gist/expected/without_overlaps.out | 6 ++--
src/backend/utils/adt/ruleutils.c | 2 +-
.../regress/expected/without_overlaps.out | 36 +++++++++----------
3 files changed, 22 insertions(+), 22 deletions(-)
diff --git a/contrib/btree_gist/expected/without_overlaps.out b/contrib/btree_gist/expected/without_overlaps.out
index 18856900ded..08b518b787e 100644
--- a/contrib/btree_gist/expected/without_overlaps.out
+++ b/contrib/btree_gist/expected/without_overlaps.out
@@ -24,9 +24,9 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rn
(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)
+ pg_get_indexdef
+------------------------------------------------------------------------
+ CREATE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at)
(1 row)
INSERT INTO temporal_rng VALUES
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 050eef97a4c..a743ca3e286 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1378,7 +1378,7 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
{
if (!isConstraint)
appendStringInfo(&buf, "CREATE %sINDEX %s ON %s%s USING %s (",
- idxrec->indisunique ? "UNIQUE " : "",
+ (idxrec->indisunique && amrec->oid == BTREE_AM_OID) ? "UNIQUE " : "",
quote_identifier(NameStr(idxrelrec->relname)),
idxrelrec->relkind == RELKIND_PARTITIONED_INDEX
&& !inherits ? "ONLY " : "",
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index f3144bdc39c..dbf31393479 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -55,9 +55,9 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rn
(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)
+ pg_get_indexdef
+------------------------------------------------------------------------
+ CREATE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at)
(1 row)
-- PK from LIKE:
@@ -148,9 +148,9 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rn
(1 row)
SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_pk';
- pg_get_indexdef
----------------------------------------------------------------------------------------
- CREATE UNIQUE INDEX temporal_rng2_pk ON temporal_rng2 USING gist (id1, id2, valid_at)
+ pg_get_indexdef
+--------------------------------------------------------------------------------
+ CREATE INDEX temporal_rng2_pk ON temporal_rng2 USING gist (id1, id2, valid_at)
(1 row)
-- PK with a custom range type:
@@ -185,9 +185,9 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_ml
(1 row)
SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_mltrng_pk';
- pg_get_indexdef
--------------------------------------------------------------------------------------
- CREATE UNIQUE INDEX temporal_mltrng_pk ON temporal_mltrng USING gist (id, valid_at)
+ pg_get_indexdef
+------------------------------------------------------------------------------
+ CREATE INDEX temporal_mltrng_pk ON temporal_mltrng USING gist (id, valid_at)
(1 row)
-- PK with two columns plus a multirange:
@@ -214,9 +214,9 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_ml
(1 row)
SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk';
- pg_get_indexdef
----------------------------------------------------------------------------------------------
- CREATE UNIQUE INDEX temporal_mltrng2_pk ON temporal_mltrng2 USING gist (id1, id2, valid_at)
+ pg_get_indexdef
+--------------------------------------------------------------------------------------
+ CREATE INDEX temporal_mltrng2_pk ON temporal_mltrng2 USING gist (id1, id2, valid_at)
(1 row)
-- UNIQUE with no columns just WITHOUT OVERLAPS:
@@ -264,9 +264,9 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rn
(1 row)
SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng3_uq';
- pg_get_indexdef
----------------------------------------------------------------------------------
- CREATE UNIQUE INDEX temporal_rng3_uq ON temporal_rng3 USING gist (id, valid_at)
+ pg_get_indexdef
+--------------------------------------------------------------------------
+ CREATE INDEX temporal_rng3_uq ON temporal_rng3 USING gist (id, valid_at)
(1 row)
DROP TABLE temporal_rng3;
@@ -294,9 +294,9 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rn
(1 row)
SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng3_uq';
- pg_get_indexdef
----------------------------------------------------------------------------------------
- CREATE UNIQUE INDEX temporal_rng3_uq ON temporal_rng3 USING gist (id1, id2, valid_at)
+ pg_get_indexdef
+--------------------------------------------------------------------------------
+ CREATE INDEX temporal_rng3_uq ON temporal_rng3 USING gist (id1, id2, valid_at)
(1 row)
DROP TABLE temporal_rng3;
--
2.51.0
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-10-29 15:03 Kirill Reshke <reshkekirill@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 16+ messages in thread
From: Kirill Reshke @ 2025-10-29 15:03 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: n.bartek3762@gmail.com; pgsql-bugs@lists.postgresql.org
On Wed, 29 Oct 2025 at 18:52, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Wed, 2025-10-29 at 17:43 +0500, Kirill Reshke wrote:
> > On Wed, 29 Oct 2025 at 16:31, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > >
> > >
> > > I think I see what you mean:
> > >
> > > CREATE TABLE temp (
> > > id bigint NOT NULL,
> > > valid tstzrange NOT NULL,
> > > CONSTRAINT temp_pkey PRIMARY KEY (id, valid WITHOUT OVERLAPS)
> > > );
> > >
> > > SELECT pg_get_indexdef('temp_pkey'::regclass);
> > >
> > > pg_get_indexdef
> > > ----------------------------------------------------------------------
> > > CREATE UNIQUE INDEX temp_pkey ON laurenz.temp USING gist (id, valid)
> > >
> > > That CREATE INDEX statement won't work.
> > >
> > > Yours,
> > > Laurenz Albe
> > >
> > >
> >
> > Yep, this is probably a valid rewording of the first email. Is it a
> > problem that is worth fixing?
>
> I personally think so, although the fact that this occurs in the regression tests
> might mean that it is intentional.
>
> Anyway, here is a patch that fixes the problem for me. I am not happy with it,
> because it hard-codes that CREATE UNIQUE INDEX only works with B-tree indexes,
> but I don't have a better idea.
>
> Yours,
> Laurenz Albe
indeed, this has been discussed [0]
After brief reading, it looks like this is just an oversight, CREATE
UNIQUE INDEX ... USING gist implementation was discussed but never
implemented...
Your fix WFM, but if we decide this is not the correct way to fix an
issue, should we at least document it?
[0] https://www.postgresql.org/message-id/37d3137d-6bdd-4192-9f8f-da35974aa693%40illuminatedcomputing.co...
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-10-29 15:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 16+ messages in thread
From: Tom Lane @ 2025-10-29 15:07 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: n.bartek3762@gmail.com, pgsql-bugs@lists.postgresql.org, "Paul A. Jungwirth" <pj@illuminatedcomputing.com>; Peter Eisentraut <peter@eisentraut.org>
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> I think I see what you mean:
> CREATE TABLE temp (
> id bigint NOT NULL,
> valid tstzrange NOT NULL,
> CONSTRAINT temp_pkey PRIMARY KEY (id, valid WITHOUT OVERLAPS)
> );
[ Note that this example requires having installed btree_gist ]
> SELECT pg_get_indexdef('temp_pkey'::regclass);
> pg_get_indexdef
> ----------------------------------------------------------------------
> CREATE UNIQUE INDEX temp_pkey ON laurenz.temp USING gist (id, valid)
> That CREATE INDEX statement won't work.
Yes. That is a pretty serious oversight in fc0438b4e. But I don't
think your patch proposal of just suppressing the UNIQUE keyword in
pg_get_indexdef_worker improves matters, because that presumably would
result in building an index that doesn't enforce the constraint.
I have not looked at the WITHOUT OVERLAPS patch, but if the mechanism
underlying that is just to set pg_index.indisunique, then it seems
like a reasonable answer here is to allow this syntax. The question
we have is how CREATE INDEX can know that UNIQUE is supported for
index types other than btrees.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-10-29 15:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2025-10-29 15:30 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: n.bartek3762@gmail.com, pgsql-bugs@lists.postgresql.org, "Paul A. Jungwirth" <pj@illuminatedcomputing.com>; Peter Eisentraut <peter@eisentraut.org>
I wrote:
> I have not looked at the WITHOUT OVERLAPS patch, but if the mechanism
> underlying that is just to set pg_index.indisunique, then it seems
> like a reasonable answer here is to allow this syntax.
On second thought, not really, because it'd preclude ever supporting
"normal" unique indexes with GiST. Really the only thing I can
think of that isn't a complete violation of pg_get_indexdef's contract
to produce a correct representation of the index is for it to emit
an ALTER TABLE ADD CONSTRAINT command to represent these indexes.
Which seems like kind of a mess, not only because it will likely
require a deal of extra code in ruleutils, but because it will likely
break calling applications that aren't expecting such syntax.
I wonder how hard it would be to extend CREATE INDEX so that it
could produce a non-phony representation of such indexes, with the
&&-semantics columns clearly distinguished from the =-semantics ones.
Is including an opclass name sufficient, or is there some additional
secret sauce for the temporal columns?
fc0438b4e could perhaps have spent a bit longer in the oven.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-10-29 18:51 Paul A Jungwirth <pj@illuminatedcomputing.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 16+ messages in thread
From: Paul A Jungwirth @ 2025-10-29 18:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; n.bartek3762@gmail.com, pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>; Matthias van de Meent <boekewurm+postgres@gmail.com>
On Wed, Oct 29, 2025 at 8:30 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
> > I have not looked at the WITHOUT OVERLAPS patch, but if the mechanism
> > underlying that is just to set pg_index.indisunique, then it seems
> > like a reasonable answer here is to allow this syntax.
>
> On second thought, not really, because it'd preclude ever supporting
> "normal" unique indexes with GiST. Really the only thing I can
> think of that isn't a complete violation of pg_get_indexdef's contract
> to produce a correct representation of the index is for it to emit
> an ALTER TABLE ADD CONSTRAINT command to represent these indexes.
>
> Which seems like kind of a mess, not only because it will likely
> require a deal of extra code in ruleutils, but because it will likely
> break calling applications that aren't expecting such syntax.
>
> I wonder how hard it would be to extend CREATE INDEX so that it
> could produce a non-phony representation of such indexes, with the
> &&-semantics columns clearly distinguished from the =-semantics ones.
> Is including an opclass name sufficient, or is there some additional
> secret sauce for the temporal columns?
This came up a few times in hallway conversations at PgCon{,f} as well
as on the mailing list. I would really like to clean it up. I think we
have the pieces we need, but I'd appreciate some design discussion,
because there a few overlapping issues.
As you say, the basic problem is that you can create a unique
constraint (which creates an index), but you can't create just the
unique index. Matthias (cc'd) ran into this when he wanted to first
create the index concurrently, then create the constraint USING INDEX
(to avoid long locks).[0]
Also as he points out, our error message is confusing: "access method
"gist" does not support unique indexes". It does support them, sort
of. Or it can *sometimes* support them.
I would really like to support this workflow, but there are some
bigger pieces missing.
Today we can't concurrently create an exclusion constraint (which is
what temporal PKs really are). There have been attempts in 2012 and
2019 to at least concurrently REINDEX, but neither was successful (for
exclusion constraints).[1, 2]
Could we at least support the non-concurrent case? That is within
reach if we address a few things:
First, an exclusion constraint's index alone won't enforce the
constraint, unlike a unique index. Similarly a temporal unique index
is strictly more restrictive than a regular unique index, so it has to
know that it's a temporal index. To support arbitrary exclusion
constraints, we would have to move pg_constraint.conexclop over to
pg_index (or something like that). That seems messy.
On the other hand, to enforce just temporal indexes, we don't need any
new columns on pg_index. We can identify temporal indexes already
because they have both indisunique and indisexclusion. So GiST could
do the right thing here. Perhaps we would have a new can_temporal
property for index AMs.
Also we need syntax on CREATE INDEX to say what you want. The SQL
standard doesn't offer any help here. It gives syntax for WITHOUT
OVERLAPS in primary key and unique constraints, but it says nothing
about WITHOUT OVERLAPS on indexes.
One option is to add WITHOUT OVERLAPS syntax to the index column list.
But those are full expressions (unlike with constraints), and you can
give optional opclasses and collations. There might be parser
conflicts.
Maybe we should use the existing opclass_parameter syntax instead.
These get stored in pg_attribute.attoptions (for the index rel). That
seems safer and more extensible. It avoids future conflicts with the
standard. And it leaves the door open to eventually supporting
arbitrary exclusion constraint operators. I haven't looked deeply at
this approach to the syntax, but it seems like the most promising.
(Just the opclass *name* is not sufficient, since the point is you
have to know to use overlaps semantics, not equality, and forbid empty
values.)
Once we have this syntax, pg_get_indexdef can use it. Likewise we
could allow USING INDEX for a WITHOUT OVERLAPS constraint (as long as
the index was enforcing temporal semantics).
Whatever we do here, we should also consider how it affects
pg_indexam_has_property, pg_index_has_property, and
pg_index_column_has_property. What does the false mean from
`pg_indexam_has_property((select oid from pg_am where amname =
'gist'), 'can_unique')`? We really want a "maybe" or "sometimes"
answer. Should we return NULL and support 'can_unique' in the other
more-specific columns?
Note that GiST theoretically can support unique indexes,[3] but we
would need a way to know which operator (or strategy number) the
opclass wanted for equality. I don't think there are any other
blockers. But we've solved that now with the CompareType enum.[4] So
that gives us a path to make GiST be unequivocally true for
can_unique. Then the has_property functions don't need big changes.
And it would solve the misleading error message.
You still couldn't pre-create the index *concurrently*, but all the
other pieces would be in place for the non-locking workflow.
So here is a proposed sequence of work:
- Add an opclass_parameter so you can say without_overlaps = true.
Only the last column of the index allows that (at least for now), and
there must be an overlaps operator.
- If an index has that property, enforce the exclusion constraint
rules and forbid empty ranges/multiranges.
- Update pg_get_indexdef to output the right syntax to create an
independent temporal index.
- Allow USING INDEX for WITHOUT OVERLAPS. It must be a temporal index.
- Support can_unique in GiST indexes using CompareType. (Perhaps this
is of low practical value, but the discrepancies bug me.)
- Add can_temporal to index AMs. Make GiST report true. Check this
property instead of hard-coding GiST in our WITHOUT OVERLAPS code.
- Support reindexing these indexes concurrently. (I'm not signing up
for this just yet, but maybe someday.)
- Support creating these indexes concurrently. (I'm not signing up for
this just yet, but maybe someday.)
[0] Mattias's thread about pre-creating the index:
https://www.postgresql.org/message-id/CAEze2WiD%2BU1BuJDLGL%3DFXxa8hDxNALVE6Jij0cNXjp10Q%3DnZHw%40ma...
[1] REINDEX concurrently original patch thread from 2012:
https://www.postgresql.org/message-id/flat/CAB7nPqS%2BWYN021oQHd9GPe_5dSVcVXMvEBW_E2AV9OOEwggMHw%40m...
[2] REINDEX concurrently revised patch thread, committed in 2019:
https://www.postgresql.org/message-id/flat/60052986-956b-4478-45ed-8bd119e9b9cf%402ndquadrant.com#74...
[3] GiST paper: https://dsf.berkeley.edu/papers/sigmod97-gist.pdf
[4] CompareType: 630f9a43cec
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-10-30 10:30 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Paul A Jungwirth <pj@illuminatedcomputing.com>
1 sibling, 1 reply; 16+ messages in thread
From: Laurenz Albe @ 2025-10-30 10:30 UTC (permalink / raw)
To: Paul A Jungwirth <pj@illuminatedcomputing.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: n.bartek3762@gmail.com, pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>; Matthias van de Meent <boekewurm+postgres@gmail.com>
On Wed, 2025-10-29 at 11:51 -0700, Paul A Jungwirth wrote:
> So here is a proposed sequence of work:
>
> - Add an opclass_parameter so you can say without_overlaps = true.
> Only the last column of the index allows that (at least for now), and
> there must be an overlaps operator.
> - If an index has that property, enforce the exclusion constraint
> rules and forbid empty ranges/multiranges.
> - Update pg_get_indexdef to output the right syntax to create an
> independent temporal index.
> - Allow USING INDEX for WITHOUT OVERLAPS. It must be a temporal index.
> - Support can_unique in GiST indexes using CompareType. (Perhaps this
> is of low practical value, but the discrepancies bug me.)
> - Add can_temporal to index AMs. Make GiST report true. Check this
> property instead of hard-coding GiST in our WITHOUT OVERLAPS code.
> - Support reindexing these indexes concurrently. (I'm not signing up
> for this just yet, but maybe someday.)
> - Support creating these indexes concurrently. (I'm not signing up for
> this just yet, but maybe someday.)
That's perhaps a good way for the future, but the cat is already out
of the bag. Do you have any ideas what to do about v18?
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-10-30 17:20 Paul A Jungwirth <pj@illuminatedcomputing.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 16+ messages in thread
From: Paul A Jungwirth @ 2025-10-30 17:20 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; n.bartek3762@gmail.com, pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>; Matthias van de Meent <boekewurm+postgres@gmail.com>
On Thu, Oct 30, 2025 at 3:30 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Wed, 2025-10-29 at 11:51 -0700, Paul A Jungwirth wrote:
> > So here is a proposed sequence of work:
> >
> > - Add an opclass_parameter so you can say without_overlaps = true.
> > Only the last column of the index allows that (at least for now), and
> > there must be an overlaps operator.
> > - If an index has that property, enforce the exclusion constraint
> > rules and forbid empty ranges/multiranges.
> > - Update pg_get_indexdef to output the right syntax to create an
> > independent temporal index.
> > - Allow USING INDEX for WITHOUT OVERLAPS. It must be a temporal index.
> > - Support can_unique in GiST indexes using CompareType. (Perhaps this
> > is of low practical value, but the discrepancies bug me.)
> > - Add can_temporal to index AMs. Make GiST report true. Check this
> > property instead of hard-coding GiST in our WITHOUT OVERLAPS code.
> > - Support reindexing these indexes concurrently. (I'm not signing up
> > for this just yet, but maybe someday.)
> > - Support creating these indexes concurrently. (I'm not signing up for
> > this just yet, but maybe someday.)
>
> That's perhaps a good way for the future, but the cat is already out
> of the bag. Do you have any ideas what to do about v18?
Doing the first 3 items here would fix pg_get_indexdef. The hard one
is the second, but I will work on a patch for it. Is that something
we'd want to release as a patch to v18?
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-10-30 18:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Paul A Jungwirth <pj@illuminatedcomputing.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2025-10-30 18:31 UTC (permalink / raw)
To: Paul A Jungwirth <pj@illuminatedcomputing.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; n.bartek3762@gmail.com, pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>; Matthias van de Meent <boekewurm+postgres@gmail.com>
Paul A Jungwirth <pj@illuminatedcomputing.com> writes:
> So here is a proposed sequence of work:
>
> - Add an opclass_parameter so you can say without_overlaps = true.
> Only the last column of the index allows that (at least for now), and
> there must be an overlaps operator.
> - If an index has that property, enforce the exclusion constraint
> rules and forbid empty ranges/multiranges.
> - Update pg_get_indexdef to output the right syntax to create an
> independent temporal index.
> Doing the first 3 items here would fix pg_get_indexdef. The hard one
> is the second, but I will work on a patch for it. Is that something
> we'd want to release as a patch to v18?
I think that's too much risk and churn for v18 at this point.
Even if we risked putting in such a patch, we'd have a situation
where pg_get_indexdef in later v18 minor releases would output
syntax that's rejected by earlier minor releases, which would
be a mess.
My feeling is that pg_get_indexdef is broken for these indexes, but
there's nothing we can do now to improve that in v18. The important
thing is to make sure it gets fixed for 19.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-10-31 08:51 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Laurenz Albe @ 2025-10-31 08:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Paul A Jungwirth <pj@illuminatedcomputing.com>; +Cc: n.bartek3762@gmail.com, pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>; Matthias van de Meent <boekewurm+postgres@gmail.com>
On Thu, 2025-10-30 at 14:31 -0400, Tom Lane wrote:
> Paul A Jungwirth <pj@illuminatedcomputing.com> writes:
> > So here is a proposed sequence of work:
> >
> > - Add an opclass_parameter so you can say without_overlaps = true.
> > Only the last column of the index allows that (at least for now), and
> > there must be an overlaps operator.
> > - If an index has that property, enforce the exclusion constraint
> > rules and forbid empty ranges/multiranges.
> > - Update pg_get_indexdef to output the right syntax to create an
> > independent temporal index.
>
> > Doing the first 3 items here would fix pg_get_indexdef. The hard one
> > is the second, but I will work on a patch for it. Is that something
> > we'd want to release as a patch to v18?
>
> I think that's too much risk and churn for v18 at this point.
> Even if we risked putting in such a patch, we'd have a situation
> where pg_get_indexdef in later v18 minor releases would output
> syntax that's rejected by earlier minor releases, which would
> be a mess.
>
> My feeling is that pg_get_indexdef is broken for these indexes, but
> there's nothing we can do now to improve that in v18. The important
> thing is to make sure it gets fixed for 19.
One thing we could do is have pg_get_indexdef() throw an error rather
than producing wrong output.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-10-31 09:11 Kirill Reshke <reshkekirill@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 16+ messages in thread
From: Kirill Reshke @ 2025-10-31 09:11 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Paul A Jungwirth <pj@illuminatedcomputing.com>; n.bartek3762@gmail.com, pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>; Matthias van de Meent <boekewurm+postgres@gmail.com>
On Fri, 31 Oct 2025 at 13:52, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Thu, 2025-10-30 at 14:31 -0400, Tom Lane wrote:
> > Paul A Jungwirth <pj@illuminatedcomputing.com> writes:
> > > So here is a proposed sequence of work:
> > >
> > > - Add an opclass_parameter so you can say without_overlaps = true.
> > > Only the last column of the index allows that (at least for now), and
> > > there must be an overlaps operator.
> > > - If an index has that property, enforce the exclusion constraint
> > > rules and forbid empty ranges/multiranges.
> > > - Update pg_get_indexdef to output the right syntax to create an
> > > independent temporal index.
> >
> > > Doing the first 3 items here would fix pg_get_indexdef. The hard one
> > > is the second, but I will work on a patch for it. Is that something
> > > we'd want to release as a patch to v18?
> >
> > I think that's too much risk and churn for v18 at this point.
> > Even if we risked putting in such a patch, we'd have a situation
> > where pg_get_indexdef in later v18 minor releases would output
> > syntax that's rejected by earlier minor releases, which would
> > be a mess.
> >
> > My feeling is that pg_get_indexdef is broken for these indexes, but
> > there's nothing we can do now to improve that in v18. The important
> > thing is to make sure it gets fixed for 19.
>
> One thing we could do is have pg_get_indexdef() throw an error rather
> than producing wrong output.
>
> Yours,
> Laurenz Albe
>
Yep, errcode(FEATURE_NOT_SUPPORTED) here may be a lesser evil compared
to others.
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-10-31 11:22 Matthias van de Meent <boekewurm+postgres@gmail.com>
parent: Paul A Jungwirth <pj@illuminatedcomputing.com>
1 sibling, 1 reply; 16+ messages in thread
From: Matthias van de Meent @ 2025-10-31 11:22 UTC (permalink / raw)
To: Paul A Jungwirth <pj@illuminatedcomputing.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; n.bartek3762@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Peter Eisentraut <peter@eisentraut.org>; Matthias van de Meent <boekewurm+postgres@gmail.com>
Hi,
Thanks for the CC, and sorry for the delayed response. I've been
travelling, and will be for another week or so.
On Wed, 29 Oct 2025, 19:51 Paul A Jungwirth,
<pj@illuminatedcomputing.com> wrote:
>
> On Wed, Oct 29, 2025 at 8:30 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > I wrote:
> > > I have not looked at the WITHOUT OVERLAPS patch, but if the mechanism
> > > underlying that is just to set pg_index.indisunique, then it seems
> > > like a reasonable answer here is to allow this syntax.
> >
> > On second thought, not really, because it'd preclude ever supporting
> > "normal" unique indexes with GiST. Really the only thing I can
> > think of that isn't a complete violation of pg_get_indexdef's contract
> > to produce a correct representation of the index is for it to emit
> > an ALTER TABLE ADD CONSTRAINT command to represent these indexes.
> >
> > Which seems like kind of a mess, not only because it will likely
> > require a deal of extra code in ruleutils, but because it will likely
> > break calling applications that aren't expecting such syntax.
> >
> > I wonder how hard it would be to extend CREATE INDEX so that it
> > could produce a non-phony representation of such indexes, with the
> > &&-semantics columns clearly distinguished from the =-semantics ones.
> > Is including an opclass name sufficient, or is there some additional
> > secret sauce for the temporal columns?
>
> This came up a few times in hallway conversations at PgCon{,f} as well
> as on the mailing list. I would really like to clean it up. I think we
> have the pieces we need, but I'd appreciate some design discussion,
> because there a few overlapping issues.
>
> As you say, the basic problem is that you can create a unique
> constraint (which creates an index), but you can't create just the
> unique index. Matthias (cc'd) ran into this when he wanted to first
> create the index concurrently, then create the constraint USING INDEX
> (to avoid long locks).[0]
>
> Also as he points out, our error message is confusing: "access method
> "gist" does not support unique indexes". It does support them, sort
> of. Or it can *sometimes* support them.
Well, strictly speaking, GIST itself doesn't do unique indexes. There
are hacked unique constraints which rely on the GIST AM and that
marked those GIST indexes as unique, but GIST itself doesn't know
about or validate any of the uniqueness - the uniqueness is instead
guaranteed by higher level systems. Which is also my main issue with
the new code - it is secretly an exclusion constraint under the hood,
so instead of leveraging the AM to guarantee the uniqueness constraint
it instead relies on repeated calls into the AM to validate the
constraint.
I'm not a fan of this disjoint uniqueness handling. It's the main
reason why we can't build these indexes concurrently, and if we want
the *index* to be considered unique, we should also make the *index*
guarantee that uniqueness, not some higher code.
> I would really like to support this workflow, but there are some
> bigger pieces missing.
>
> Today we can't concurrently create an exclusion constraint (which is
> what temporal PKs really are). There have been attempts in 2012 and
> 2019 to at least concurrently REINDEX, but neither was successful (for
> exclusion constraints).[1, 2]
I don't see why we would need to support concurrent creation of
exclusion constraints to be able to rebuild indexes concurrently.
CREATE CONSTRAINT USING INDEX ... NOT VALID + VALIDATE CONSTRAINT
works for that, right?
> Could we at least support the non-concurrent case? That is within
> reach if we address a few things:
>
> First, an exclusion constraint's index alone won't enforce the
> constraint, unlike a unique index. Similarly a temporal unique index
> is strictly more restrictive than a regular unique index, so it has to
> know that it's a temporal index.
I really dislike the term "temporal index", as there is nothing
temporal about the index. Presumably it's unique, with WITHOUT
OVERLAPS behaviour, but none of that has anything to do with temporal
- only uniqueness and non-unitary data types.
> To support arbitrary exclusion
> constraints, we would have to move pg_constraint.conexclop over to
> pg_index (or something like that). That seems messy.
Yes, that'd be messy. But I'm not sure why we would need to move over
pg_constraint.conexclop -- we're working with something called unique
indexes here, right?
> On the other hand, to enforce just temporal indexes, we don't need any
> new columns on pg_index. We can identify temporal indexes already
> because they have both indisunique and indisexclusion. So GiST could
> do the right thing here. Perhaps we would have a new can_temporal
> property for index AMs.
In my opinion, if an index is marked unique, then that index ('s AM)
should be responsible for fully validating that uniqueness; this would
include the WITHOUT OVERLAPS support that was added to UNIQUE
constraints in PG 18 (though apparently it isn't).
AFAIK, the pg_index data should be sufficient to support this, and
that should allow us to remove the hack that made indexes exist that a
user can't create. We can validate whether the index's opclasses
support this check and whether the index can actually validate
uniqueness, but I find it weird that I can interact (in C) with an
index marked unique without that index making sure the checks
happened. Btree does it, so why not GIST?
> Also we need syntax on CREATE INDEX to say what you want. The SQL
> standard doesn't offer any help here. It gives syntax for WITHOUT
> OVERLAPS in primary key and unique constraints, but it says nothing
> about WITHOUT OVERLAPS on indexes.
>
> One option is to add WITHOUT OVERLAPS syntax to the index column list.
> But those are full expressions (unlike with constraints), and you can
> give optional opclasses and collations. There might be parser
> conflicts.
I don't think there would be a parser conflict there: the only
production it might conflict with is the opclass' name, but that's
either a single word or a quoted identifier and followed by a
parenthesized list (or the end of the index attribute syntax); whilst
WITHOUT OVERLAPS is always exactly two unquoted words. I don't think
there is a way to confuse these two; at least not yet.
> Maybe we should use the existing opclass_parameter syntax instead.
I don't like that. Opclasses shouldn't have to deal with determining
whether WITHOUT OVERLAPS is applied, they only should provide the
operator used for those checks and/or determining whether the value is
considered "empty" i.e. whether the value can overlap with another
(same) value. Both of which should be unique (no alternative
implementations) for any given opclass.
> These get stored in pg_attribute.attoptions (for the index rel). That
> seems safer and more extensible. It avoids future conflicts with the
> standard.
I'd rather have this added to pg_index as either a bit in e.g.
indoption (the new bit indicating the attribute should use WITHOUT
OVERLAPS), or as new array indicating the operators used for checking
uniqueness.
I don't think this issue warrants new additions to pg_attribute, which
is already a very wide structure.
> (Just the opclass *name* is not sufficient, since the point is you
> have to know to use overlaps semantics, not equality, and forbid empty
> values.)
Shouldn't that be implemented by the AM and delegated to the opclass
with a single support function call? I don't see why we need to
determine at runtime which operator is responsible for exclude
constraints -- do you have an example where a single opclass (not:
opfamily) needs to support multiple WITHOUT OVERLAPS -backing
operators?
> Once we have this syntax, pg_get_indexdef can use it. Likewise we
> could allow USING INDEX for a WITHOUT OVERLAPS constraint (as long as
> the index was enforcing temporal semantics).
>
> Whatever we do here, we should also consider how it affects
> pg_indexam_has_property, pg_index_has_property, and
> pg_index_column_has_property. What does the false mean from
> `pg_indexam_has_property((select oid from pg_am where amname =
> 'gist'), 'can_unique')`? We really want a "maybe" or "sometimes"
> answer. Should we return NULL and support 'can_unique' in the other
> more-specific columns?
Why? Btree has can_unique, but that doesn't mean every index is a
unique index. Surely we can add an IndexAM callback that validates
that a given index is actually something that this index AM can
support; or make ambuild/ambuildempty raise if it can't actually
construct that empty index with the given index definition.
> Note that GiST theoretically can support unique indexes,[3] but we
> would need a way to know which operator (or strategy number) the
> opclass wanted for equality.
That's only a support function away, though. AFAIK, there is only one
natural operator for equality for any opclass, and only one natural
WITHOUT OVERLAPS behaviour. If the user wants different equality or
WITHOUT OVERLAPS behaviour, then they can use a different opclass. See
e.g. btree's text_ops vs text_pattern_ops.
> I don't think there are any other
> blockers. But we've solved that now with the CompareType enum.[4] So
> that gives us a path to make GiST be unequivocally true for
> can_unique. Then the has_property functions don't need big changes.
> And it would solve the misleading error message.
>
> You still couldn't pre-create the index *concurrently*, but all the
> other pieces would be in place for the non-locking workflow.
>
> So here is a proposed sequence of work:
>
> - Add an opclass_parameter so you can say without_overlaps = true.
> Only the last column of the index allows that (at least for now), and
> there must be an overlaps operator.
I've argued this before, but any passing of knowledge about which
attribute of the index is being compared/operated on _in the opclass'
code_ should be considered a layering violation. So how would an
opclass know that it was given the final key attribute of the index
and error out if it wasn't?
> - If an index has that property, enforce the exclusion constraint
> rules and forbid empty ranges/multiranges.
How do you propose to pass this information on to the index AM?
> - Update pg_get_indexdef to output the right syntax to create an
> independent temporal index.
> - Allow USING INDEX for WITHOUT OVERLAPS. It must be a temporal index.
What's a "temporal index" in this context? Do you mean "unique index,
with some attributes using the more strict WITHOUT OVERLAPS
behaviour"?
> - Support can_unique in GiST indexes using CompareType. (Perhaps this
> is of low practical value, but the discrepancies bug me.)
What do you mean by this? Do you have a reference to discussions that
touched this topic?
> - Add can_temporal to index AMs. Make GiST report true. Check this
> property instead of hard-coding GiST in our WITHOUT OVERLAPS code.
+1; though IMO that should be implemented regardless of earlier operations.
> - Support reindexing these indexes concurrently. (I'm not signing up
> for this just yet, but maybe someday.)
> - Support creating these indexes concurrently. (I'm not signing up for
> this just yet, but maybe someday.)
+1
Kind regards,
Matthias van de Meent
Databricks (https://www.databricks.com)
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2025-12-31 22:47 Paul A Jungwirth <pj@illuminatedcomputing.com>
parent: Matthias van de Meent <boekewurm+postgres@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Paul A Jungwirth @ 2025-12-31 22:47 UTC (permalink / raw)
To: Matthias van de Meent <boekewurm+postgres@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; n.bartek3762@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Peter Eisentraut <peter@eisentraut.org>
On Fri, Oct 31, 2025 at 4:22 AM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
> Thanks for the CC, and sorry for the delayed response. I've been
> travelling, and will be for another week or so.
Thanks for the detailed response! I have an early patch to address this
(attached). It's not finished, but I thought I'd share it before the
commitfest deadline in case anyone wanted to take a look.
> Well, strictly speaking, GIST itself doesn't do unique indexes. There
> are hacked unique constraints which rely on the GIST AM and that
> marked those GIST indexes as unique, but GIST itself doesn't know
> about or validate any of the uniqueness - the uniqueness is instead
> guaranteed by higher level systems. Which is also my main issue with
> the new code - it is secretly an exclusion constraint under the hood,
> so instead of leveraging the AM to guarantee the uniqueness constraint
> it instead relies on repeated calls into the AM to validate the
> constraint.
>
> I'm not a fan of this disjoint uniqueness handling. It's the main
> reason why we can't build these indexes concurrently, and if we want
> the *index* to be considered unique, we should also make the *index*
> guarantee that uniqueness, not some higher code.
My understanding is that the main motivation behind exclusion
constraints was for temporal tables, so it seemed that was the right
tool to build temporal PK & UNIQUE constraints on top of. I see your
point about a layering violation, but on the other hand it doesn't seem
like indexes are strictly "lower" than constraints in general. On
pg_index we have indisexclusion and indisprimary. But this patch does
let us move the work to where it belongs (see below).
> > I would really like to support this workflow, but there are some
> > bigger pieces missing.
> >
> > Today we can't concurrently create an exclusion constraint (which is
> > what temporal PKs really are). There have been attempts in 2012 and
> > 2019 to at least concurrently REINDEX, but neither was successful (for
> > exclusion constraints).[1, 2]
>
> I don't see why we would need to support concurrent creation of
> exclusion constraints to be able to rebuild indexes concurrently.
>
> CREATE CONSTRAINT USING INDEX ... NOT VALID + VALIDATE CONSTRAINT
> works for that, right?
Good point. Creating the index concurrently, even if you have to
validate the constraint afterward, reduces most of the locking time.
> > Could we at least support the non-concurrent case? That is within
> > reach if we address a few things:
> >
> > First, an exclusion constraint's index alone won't enforce the
> > constraint, unlike a unique index. Similarly a temporal unique index
> > is strictly more restrictive than a regular unique index, so it has to
> > know that it's a temporal index.
>
> I really dislike the term "temporal index", as there is nothing
> temporal about the index. Presumably it's unique, with WITHOUT
> OVERLAPS behaviour, but none of that has anything to do with temporal
> - only uniqueness and non-unitary data types.
The reason I've used that term is because WITHOUT OVERLAPS has an extra
check beyond regular exclusion constraints: it has to forbid empty
ranges/multiranges. But I agree "temporal" is too narrow. We could talk
about "without overlaps indexes" instead.
> > To support arbitrary exclusion
> > constraints, we would have to move pg_constraint.conexclop over to
> > pg_index (or something like that). That seems messy.
>
> Yes, that'd be messy. But I'm not sure why we would need to move over
> pg_constraint.conexclop -- we're working with something called unique
> indexes here, right?
It depends if we want to enforce "without overlaps" as well. The
standard calls those "unique". And the most practical reason for this
work is to allow concurrently creating the index they need, and then
creating the constraint with a short lock time. If we had more than a
bitvector we could even support arbitrary exclusion constraints.
> > On the other hand, to enforce just temporal indexes, we don't need any
> > new columns on pg_index. We can identify temporal indexes already
> > because they have both indisunique and indisexclusion. So GiST could
> > do the right thing here. Perhaps we would have a new can_temporal
> > property for index AMs.
>
> In my opinion, if an index is marked unique, then that index ('s AM)
> should be responsible for fully validating that uniqueness; this would
> include the WITHOUT OVERLAPS support that was added to UNIQUE
> constraints in PG 18 (though apparently it isn't).
>
> AFAIK, the pg_index data should be sufficient to support this, and
> that should allow us to remove the hack that made indexes exist that a
> user can't create. We can validate whether the index's opclasses
> support this check and whether the index can actually validate
> uniqueness, but I find it weird that I can interact (in C) with an
> index marked unique without that index making sure the checks
> happened. Btree does it, so why not GIST?
>
> > Also we need syntax on CREATE INDEX to say what you want. The SQL
> > standard doesn't offer any help here. It gives syntax for WITHOUT
> > OVERLAPS in primary key and unique constraints, but it says nothing
> > about WITHOUT OVERLAPS on indexes.
> >
> > One option is to add WITHOUT OVERLAPS syntax to the index column list.
> > But those are full expressions (unlike with constraints), and you can
> > give optional opclasses and collations. There might be parser
> > conflicts.
>
> I don't think there would be a parser conflict there: the only
> production it might conflict with is the opclass' name, but that's
> either a single word or a quoted identifier and followed by a
> parenthesized list (or the end of the index attribute syntax); whilst
> WITHOUT OVERLAPS is always exactly two unquoted words. I don't think
> there is a way to confuse these two; at least not yet.
I tried adding an optional WITHOUT OVERLAPS modifier to index elements
in gram.y, but I did get parser conflicts. I tried several different
positions wrt the opclass and other modifiers, but there was always at
least one conflict.
> > Maybe we should use the existing opclass_parameter syntax instead.
>
> I don't like that. Opclasses shouldn't have to deal with determining
> whether WITHOUT OVERLAPS is applied, they only should provide the
> operator used for those checks and/or determining whether the value is
> considered "empty" i.e. whether the value can overlap with another
> (same) value. Both of which should be unique (no alternative
> implementations) for any given opclass.
>
> > These get stored in pg_attribute.attoptions (for the index rel). That
> > seems safer and more extensible. It avoids future conflicts with the
> > standard.
>
> I'd rather have this added to pg_index as either a bit in e.g.
> indoption (the new bit indicating the attribute should use WITHOUT
> OVERLAPS), or as new array indicating the operators used for checking
> uniqueness.
> I don't think this issue warrants new additions to pg_attribute, which
> is already a very wide structure.
That new array is what I meant by moving conexclop to pg_index (or at
least adding its equivalent). I kind of like having a bit in indoption.
I was actually doing that in a very early version of the temporal PKs
patch. I took it out because indoption seemed to be for *storage*
options. But there is plenty of room there, and I don't see any reason
not to expand its use. Well one reason: it seems like this column is
reserved for opclasses to do what they want with, and WITHOUT OVERLAPS
is core code. Is that a problem?
> > (Just the opclass *name* is not sufficient, since the point is you
> > have to know to use overlaps semantics, not equality, and forbid empty
> > values.)
>
> Shouldn't that be implemented by the AM and delegated to the opclass
> with a single support function call? I don't see why we need to
> determine at runtime which operator is responsible for exclude
> constraints -- do you have an example where a single opclass (not:
> opfamily) needs to support multiple WITHOUT OVERLAPS -backing
> operators?
>
> > Once we have this syntax, pg_get_indexdef can use it. Likewise we
> > could allow USING INDEX for a WITHOUT OVERLAPS constraint (as long as
> > the index was enforcing temporal semantics).
> >
> > Whatever we do here, we should also consider how it affects
> > pg_indexam_has_property, pg_index_has_property, and
> > pg_index_column_has_property. What does the false mean from
> > `pg_indexam_has_property((select oid from pg_am where amname =
> > 'gist'), 'can_unique')`? We really want a "maybe" or "sometimes"
> > answer. Should we return NULL and support 'can_unique' in the other
> > more-specific columns?
>
> Why? Btree has can_unique, but that doesn't mean every index is a
> unique index. Surely we can add an IndexAM callback that validates
> that a given index is actually something that this index AM can
> support; or make ambuild/ambuildempty raise if it can't actually
> construct that empty index with the given index definition.
>
> > Note that GiST theoretically can support unique indexes,[3] but we
> > would need a way to know which operator (or strategy number) the
> > opclass wanted for equality.
>
> That's only a support function away, though. AFAIK, there is only one
> natural operator for equality for any opclass, and only one natural
> WITHOUT OVERLAPS behaviour. If the user wants different equality or
> WITHOUT OVERLAPS behaviour, then they can use a different opclass. See
> e.g. btree's text_ops vs text_pattern_ops.
Okay.
> > I don't think there are any other
> > blockers. But we've solved that now with the CompareType enum.[4] So
> > that gives us a path to make GiST be unequivocally true for
> > can_unique. Then the has_property functions don't need big changes.
> > And it would solve the misleading error message.
> >
> > You still couldn't pre-create the index *concurrently*, but all the
> > other pieces would be in place for the non-locking workflow.
> >
> > So here is a proposed sequence of work:
> >
> > - Add an opclass_parameter so you can say without_overlaps = true.
> > Only the last column of the index allows that (at least for now), and
> > there must be an overlaps operator.
>
> I've argued this before, but any passing of knowledge about which
> attribute of the index is being compared/operated on _in the opclass'
> code_ should be considered a layering violation. So how would an
> opclass know that it was given the final key attribute of the index
> and error out if it wasn't?
>
> > - If an index has that property, enforce the exclusion constraint
> > rules and forbid empty ranges/multiranges.
>
> How do you propose to pass this information on to the index AM?
>
> > - Update pg_get_indexdef to output the right syntax to create an
> > independent temporal index.
> > - Allow USING INDEX for WITHOUT OVERLAPS. It must be a temporal index.
>
> What's a "temporal index" in this context? Do you mean "unique index,
> with some attributes using the more strict WITHOUT OVERLAPS
> behaviour"?
>
> > - Support can_unique in GiST indexes using CompareType. (Perhaps this
> > is of low practical value, but the discrepancies bug me.)
>
> What do you mean by this? Do you have a reference to discussions that
> touched this topic?
This was based on a few hallway conversations at PGConfs. Even if you
could have a unique GiST index (and you can today, effectively, with an
exclusion constraint that is just `(id WITH =)`, it is probably
strictly worse than a B-Tree index. That's what I mean by low practical
value. But being able to build the index separately from the constraint
does give it a use.
> > - Add can_temporal to index AMs. Make GiST report true. Check this
> > property instead of hard-coding GiST in our WITHOUT OVERLAPS code.
>
> +1; though IMO that should be implemented regardless of earlier operations.
Okay.
To start, the patch here lets you create unique GiST indexes. If the
index is for a WITHOUT OVERLAPS constraint, they check for overlaps
instead of equality. (I was originally planning to defer that part, but
it was almost no extra code.)
So now I can do this:
```
[v19devel:5432][454633] postgres=# create table t (id int4range,
valid_at daterange, primary key (id, valid_at without overlaps));
CREATE TABLE
[v19devel:5432][454633] postgres=# select pg_get_indexdef('t_pkey'::regclass);
pg_get_indexdef
------------------------------------------------------------------
CREATE UNIQUE INDEX t_pkey ON public.t USING gist (id, valid_at)
(1 row)
[v19devel:5432][454633] postgres=# alter table t drop constraint t_pkey;
ALTER TABLE
[v19devel:5432][454633] postgres=# CREATE UNIQUE INDEX t_pkey ON
public.t USING gist (id, valid_at);
CREATE INDEX
[v19devel:5432][454633] postgres=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
----------+-----------+-----------+----------+---------
id | int4range | | not null |
valid_at | daterange | | not null |
Indexes:
"t_pkey" UNIQUE, gist (id, valid_at)
```
So now pg_get_indexdef's output doesn't crash. And you get back the
same *on disk* index. But its enforcement isn't quite what you started
with. Since there was no `WITHOUT OVERLAPS`, it will forbid equality
but not forbid overlaps. Still I think this gets us closer to the end.
If using indoption is okay, we can change pg_getindexdef to do that.
And if the index sees that option, it should set indisexclusion.
The next step is to permit USING INDEX for a WITHOUT OVERLAPS
constraint. That would only be permitted for indexes with
indisexclusion and an appropriate indoption.
There are still some big missing pieces from the current patch. From
the commit message:
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?
I also need to think more about concurrency issues and locking. The
original GiST research paper envisioned unique indexes (and didn't
think they needed a lot of extra consideration), and as far as I can
tell we already have the locks we need after traversing to the leaf
page. But I want to go through it more carefully.
Also I think that check_exclusion_or_unique_constraint can now skip
most of its work, if the constraint is WITHOUT OVERLAPS. I think I will
have a new patch with that improvement soon.
Rebased to 85d5bd308b.
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
Attachments:
[text/x-patch] v1-0001-Allow-unique-GiST-indexes.patch (28.2K, ../../CA+renyU4jKCxrtASJpssZmfrkWhi-+Q_PF__jxt8E23T755SPQ@mail.gmail.com/2-v1-0001-Allow-unique-GiST-indexes.patch)
download | inline diff:
From cdb24a19d52390b8a3366ca6ba2554215c83c9e7 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
@ 2026-01-02 18:32 Paul A Jungwirth <pj@illuminatedcomputing.com>
parent: Paul A Jungwirth <pj@illuminatedcomputing.com>
0 siblings, 0 replies; 16+ messages in thread
From: Paul A Jungwirth @ 2026-01-02 18:32 UTC (permalink / raw)
To: Matthias van de Meent <boekewurm+postgres@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; n.bartek3762@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Peter Eisentraut <peter@eisentraut.org>
On Wed, Dec 31, 2025 at 2:47 PM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:
>
> Thanks for the detailed response! I have an early patch to address this
> (attached). It's not finished, but I thought I'd share it before the
> commitfest deadline in case anyone wanted to take a look.
I started a thread in pgsql-hackers to continue the conversation there:
https://www.postgresql.org/message-id/CA%2BrenyUKo9GC%2Bbtg07E2Dt8998kWxSvM-4z16jUA7SOxDcJ9OQ%40mail...
--
Paul ~{:-)
pj@illuminatedcomputing.com
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2026-01-02 18:32 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-29 09:54 BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that PG Bug reporting form <noreply@postgresql.org>
2025-10-29 11:31 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-29 12:43 ` Kirill Reshke <reshkekirill@gmail.com>
2025-10-29 13:52 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-29 15:03 ` Kirill Reshke <reshkekirill@gmail.com>
2025-10-29 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-10-29 15:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-10-29 18:51 ` Paul A Jungwirth <pj@illuminatedcomputing.com>
2025-10-30 10:30 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-30 17:20 ` Paul A Jungwirth <pj@illuminatedcomputing.com>
2025-10-30 18:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-10-31 08:51 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-10-31 09:11 ` Kirill Reshke <reshkekirill@gmail.com>
2025-10-31 11:22 ` Matthias van de Meent <boekewurm+postgres@gmail.com>
2025-12-31 22:47 ` Paul A Jungwirth <pj@illuminatedcomputing.com>
2026-01-02 18:32 ` Paul A Jungwirth <pj@illuminatedcomputing.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox