public inbox for [email protected]  

help / color / mirror / Atom feed

From: Paul Jungwirth <[email protected]>
To: Matthias van de Meent <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: jian he <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: SQL:2011 application time
Date: Sun, 12 May 2024 21:54:34 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
	<CACJufxHsYDfHK62FTHdLsgDenvJd_bEuxCiyH62Lacj7NvzX4w@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CA+TgmoaH-2fAttd24pA3p45TUHbM9hRU6ud=brhKsp9ENHXWDg@mail.gmail.com>
	<[email protected]>
	<CA+TgmobfDJuXJATNUco+g6AiUb3WS2Pi9FrJe9hKAHGw37RX-g@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAEze2WiD+U1BuJDLGL=FXxa8hDxNALVE6Jij0cNXjp10Q=nZHw@mail.gmail.com>
	<[email protected]>
	<CAEze2Wh21V66udM8cbvBBsAgyQ_5x9nfR0d3sWzbmZk++ey7xw@mail.gmail.com>
	<[email protected]>

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              ~{:-)
[email protected]

view thread (10+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: SQL:2011 application time
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox