Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that - Mailing list pgsql-bugs

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%40mail.gmail.com
[1] REINDEX concurrently original patch thread from 2012:

https://www.postgresql.org/message-id/flat/CAB7nPqS%2BWYN021oQHd9GPe_5dSVcVXMvEBW_E2AV9OOEwggMHw%40mail.gmail.com#e1a372074cfdf37bf9e5b4e29ddf7b2d
[2] REINDEX concurrently revised patch thread, committed in 2019:

https://www.postgresql.org/message-id/flat/60052986-956b-4478-45ed-8bd119e9b9cf%402ndquadrant.com#74948a1044c56c5e817a5050f554ddee
[3] GiST paper: https://dsf.berkeley.edu/papers/sigmod97-gist.pdf
[4] CompareType: 630f9a43cec

Yours,

--
Paul              ~{:-)
pj@illuminatedcomputing.com