Message-ID: <37d3137d-6bdd-4192-9f8f-da35974aa693@illuminatedcomputing.com>
Date: Sun, 12 May 2024 08:51:11 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SQL:2011 application time
To: Matthias van de Meent <boekewurm+postgres@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>,
 Robert Haas <robertmhaas@gmail.com>, jian he <jian.universality@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
 <49c07eac-18ec-4aff-929c-e81b87df1092@eisentraut.org>
 <CACJufxHsYDfHK62FTHdLsgDenvJd_bEuxCiyH62Lacj7NvzX4w@mail.gmail.com>
 <d579a67c-e8d7-4eb7-ae14-f6fca01489cf@illuminatedcomputing.com>
 <1edb3b79-a4bc-43b1-80aa-2e090472fe32@eisentraut.org>
 <47550967-260b-4180-9791-b224859fe63e@illuminatedcomputing.com>
 <CA+TgmoaH-2fAttd24pA3p45TUHbM9hRU6ud=brhKsp9ENHXWDg@mail.gmail.com>
 <1b2ab118-82c1-4e79-a094-04dab382e2c4@illuminatedcomputing.com>
 <CA+TgmobfDJuXJATNUco+g6AiUb3WS2Pi9FrJe9hKAHGw37RX-g@mail.gmail.com>
 <3775839b-3f0f-4c8a-ac03-a253222e6a4b@illuminatedcomputing.com>
 <731ef81e-d4c3-41c6-ba8f-f6131f4f6407@eisentraut.org>
 <1426589a-83cb-4a89-bf40-713970c07e63@illuminatedcomputing.com>
 <CAEze2WiD+U1BuJDLGL=FXxa8hDxNALVE6Jij0cNXjp10Q=nZHw@mail.gmail.com>
 <5aefa87e-ba20-4a34-88aa-233255643c1b@illuminatedcomputing.com>
 <CAEze2Wh21V66udM8cbvBBsAgyQ_5x9nfR0d3sWzbmZk++ey7xw@mail.gmail.com>
Content-Language: en-US
From: Paul Jungwirth <pj@illuminatedcomputing.com>
In-Reply-To: <CAEze2Wh21V66udM8cbvBBsAgyQ_5x9nfR0d3sWzbmZk++ey7xw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/37d3137d-6bdd-4192-9f8f-da35974aa693%40illuminatedcomputing.com>
Precedence: bulk

On 5/12/24 05:55, Matthias van de Meent wrote:
>>   > pg=# CREATE UNIQUE INDEX ON temporal_testing USING gist (id, valid_during);
>>   > ERROR:  access method "gist" does not support unique indexes
>>
>> To me that error message seems correct. The programmer hasn't said anything about the special
>> temporal behavior they are looking for.
> 
> But I showed that I had a GIST index that does have the indisunique
> flag set, which shows that GIST does support indexes with unique
> semantics.
> 
> That I can't use CREATE UNIQUE INDEX to create such an index doesn't
> mean the feature doesn't exist, which is what the error message
> implies.

True, the error message is not really telling the truth anymore. I do think most people who hit this 
error are not thinking about temporal constraints at all though, and for non-temporal constraints it 
is still true. It's also true for CREATE INDEX, since WITHOUT OVERLAPS is only available on the 
*constraint*. So how about adding a hint, something like this?:

ERROR:  access method "gist" does not support unique indexes
HINT: To create a unique constraint with non-overlap behavior, use ADD CONSTRAINT ... WITHOUT OVERLAPS.

Yours,

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