public inbox for [email protected]
help / color / mirror / Atom feedFrom: 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: Sat, 11 May 2024 20:26:55 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAEze2WiD+U1BuJDLGL=FXxa8hDxNALVE6Jij0cNXjp10Q=nZHw@mail.gmail.com>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<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>
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 ~{:-)
[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