MIME-Version: 1.0
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
 <CACJufxF8HyERm7SKBJ+duxAP9sTgVhaW6b-oWADANGgn-o4wPA@mail.gmail.com>
 <b4bc1ef8-4215-4cf6-933c-5ef2a2827efe@illuminatedcomputing.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>
In-Reply-To: <1426589a-83cb-4a89-bf40-713970c07e63@illuminatedcomputing.com>
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
Date: Fri, 10 May 2024 02:44:08 +0200
Message-ID: <CAEze2WiD+U1BuJDLGL=FXxa8hDxNALVE6Jij0cNXjp10Q=nZHw@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/CAEze2WiD%2BU1BuJDLGL%3DFXxa8hDxNALVE6Jij0cNXjp10Q%3DnZHw%40mail.gmail.com>
Precedence: bulk

Hi,

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.

pg=# CREATE TABLE temporal_testing (
pg(#  id bigint NOT NULL
pg(#    generated always as identity,
pg(#  valid_during tstzrange
pg(# );
CREATE TABLE
pg=# ALTER TABLE temporal_testing
pg-#  ADD CONSTRAINT temp_unique UNIQUE (id, valid_during WITHOUT OVERLAPS);
ALTER TABLE
pg=# \d+ temp_unique
                         Index "public.temp_unique"
    Column    |    Type     | Key? |  Definition  | Storage  | Stats target
--------------+-------------+------+--------------+----------+--------------
 id           | gbtreekey16 | yes  | id           | plain    |
 valid_during | tstzrange   | yes  | valid_during | extended |
unique, gist, for table "public.temporal_testing"
-- ^^ note the "unique, gist"
pg=# CREATE UNIQUE INDEX ON temporal_testing USING gist (id, valid_during);
ERROR:  access method "gist" does not support unique indexes

Here we obviously have a unique GIST index in the catalogs, but
they're "not supported" by GIST when we try to create such index
ourselves (!). Either the error message needs updating, or we need to
have a facility to actually support creating these unique indexes
outside constraints.

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.

Kind regards,

Matthias van de Meent.