From: Tom Lane <tgl@sss.pgh.pa.us>
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>
Subject: Re: BUG #19098: Can't create unique gist index,
 where pg_indexes says that WITHOUT OVERLAPS does exacly that
In-reply-to: <11e73b0d2d89476fb04e6055a80638f776bb6f18.camel@cybertec.at>
References: <19098-7f8deb5093c13da2@postgresql.org>
 <11e73b0d2d89476fb04e6055a80638f776bb6f18.camel@cybertec.at>
Comments: In-reply-to Laurenz Albe <laurenz.albe@cybertec.at>
	message dated "Wed, 29 Oct 2025 12:31:13 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2748211.1761750444.1@sss.pgh.pa.us>
Date: Wed, 29 Oct 2025 11:07:24 -0400
Message-ID: <2748212.1761750444@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2748212.1761750444%40sss.pgh.pa.us>
Precedence: bulk

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