Message-ID: <5aefa87e-ba20-4a34-88aa-233255643c1b@illuminatedcomputing.com>
Date: Sat, 11 May 2024 20:26:55 -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>
 <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>
Content-Language: en-US
From: Paul Jungwirth <pj@illuminatedcomputing.com>
In-Reply-To: <CAEze2WiD+U1BuJDLGL=FXxa8hDxNALVE6Jij0cNXjp10Q=nZHw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/5aefa87e-ba20-4a34-88aa-233255643c1b%40illuminatedcomputing.com>
Precedence: bulk

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.

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. To get non-overlapping semantics from an index, this more 
explicit syntax seems better, similar to PKs in the standard:

 > 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.

> Additionally, because I can't create my own non-constraint-backing
> unique GIST indexes, I can't pre-create my unique constraints
> CONCURRENTLY as one could do for the non-temporal case: UNIQUE
> constraints hold ownership of the index and would drop the index if
> the constraint is dropped, too, and don't support a CONCURRENTLY
> modifier, nor an INVALID modifier. This means temporal unique
> constraints have much less administrative wiggle room than normal
> unique constraints, and I think that's not great.

This is a great use-case for why we should support this eventually, even if it uses non-standard syntax.

Yours,

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