Re: SQL:2011 application time

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(at)illuminatedcomputing(dot)com