MIME-Version: 1.0
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
 <85dd3ad0-fa79-46e8-a08b-c426c76825f3@eisentraut.org> <a5d8df78-9ff6-4273-8d06-6c85b5520520@illuminatedcomputing.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>
In-Reply-To: <5aefa87e-ba20-4a34-88aa-233255643c1b@illuminatedcomputing.com>
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
Date: Sun, 12 May 2024 14:55:52 +0200
Message-ID: <CAEze2Wh21V66udM8cbvBBsAgyQ_5x9nfR0d3sWzbmZk++ey7xw@mail.gmail.com>
Subject: Re: SQL:2011 application time
To: Paul Jungwirth <pj@illuminatedcomputing.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>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAEze2Wh21V66udM8cbvBBsAgyQ_5x9nfR0d3sWzbmZk%2B%2Bey7xw%40mail.gmail.com>
Precedence: bulk

On Sun, 12 May 2024 at 05:26, Paul Jungwirth
<pj@illuminatedcomputing.com> wrote:
> On 5/9/24 17:44, Matthias van de Meent wrote:
> > I haven't really been following this thread, but after playing around
> > a bit with the feature I feel there are new gaps in error messages. I
> > also think there are gaps in the functionality regarding the (lack of)
> > support for CREATE UNIQUE INDEX, and attaching these indexes to
> > constraints
> Thank you for trying this out and sharing your thoughts! I think these are good points about CREATE
> UNIQUE INDEX and then creating the constraint by handing it an existing index. This is something
> that I am hoping to add, but it's not covered by the SQL:2011 standard, so I think it needs some
> discussion, and I don't think it needs to go into v17.

Okay.

> For instance you are saying:
>
>  > 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.

> To get non-overlapping semantics from an index, this more
> explicit syntax seems better, similar to PKs in the standard:

Yes, agreed on that part.

>  > pg=# CREATE UNIQUE INDEX ON temporal_testing USING gist (id, valid_during WITHOUT OVERLAPS);
>  > ERROR:  access method "gist" does not support unique indexes
>
> We could also support *non-temporal* unique GiST indexes, particularly now that we have the stratnum
> support function. Those would use the syntax you gave, omitting WITHOUT OVERLAPS. But that seems
> like a separate effort to me.

No objection on that.

Kind regards,

Matthias van de Meent