Message-ID: <f29c051e-0fed-4c2e-9698-c3ffbbfdbb9f@illuminatedcomputing.com>
Date: Sun, 12 May 2024 21:54:34 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SQL:2011 application time
From: Paul Jungwirth <pj@illuminatedcomputing.com>
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>
 <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>
 <37d3137d-6bdd-4192-9f8f-da35974aa693@illuminatedcomputing.com>
Content-Language: en-US
In-Reply-To: <37d3137d-6bdd-4192-9f8f-da35974aa693@illuminatedcomputing.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/f29c051e-0fed-4c2e-9698-c3ffbbfdbb9f%40illuminatedcomputing.com>
Precedence: bulk

On 5/12/24 08:51, Paul Jungwirth wrote:
> 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.

I thought a little more about eventually implementing WITHOUT OVERLAPS support for CREATE INDEX, and 
how it relates to this error message in particular. Even when that is done, it will still depend on 
the stratnum support function for the keys' opclasses, so the GiST AM itself will still have false 
amcanunique, I believe. Probably the existing error message is still the right one. The hint won't 
need to mention ADD CONSTRAINT anymore. It should still point users to WITHOUT OVERLAPS, and 
possibly the stratnum support function too. I think what we are doing for v17 is all compatible with 
that plan.

Yours,

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