public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bruce Momjian <[email protected]>
To: Simon Riggs <[email protected]>
Cc: Cary Huang <[email protected]>
Cc: Pgsql Hackers <[email protected]>
Subject: Re: Patch: Global Unique Index
Date: Fri, 25 Nov 2022 12:48:12 -0500
Message-ID: <Y4D/[email protected]> (raw)
In-Reply-To: <CANbhV-Eo4D2Njpa=YiBr7BxOMDi5B54wenjgoamRKjzj-9gBGQ@mail.gmail.com>
References: <[email protected]>
<CANbhV-Eo4D2Njpa=YiBr7BxOMDi5B54wenjgoamRKjzj-9gBGQ@mail.gmail.com>
On Mon, Nov 21, 2022 at 12:33:30PM +0000, Simon Riggs wrote:
> On Thu, 17 Nov 2022 at 22:01, Cary Huang <[email protected]> wrote:
> >
> > Patch: Global Unique Index
>
> Let me start by expressing severe doubt on the usefulness of such a
> feature, but also salute your efforts to contribute.
>
> > In other words, a global unique index and a regular partitioned index are essentially the same in terms of their storage structure except that one can do cross-partition uniqueness check, the other cannot.
>
> This is the only workable architecture, since it allows DETACH to be
> feasible, which is essential.
I had trouble understanding this feature so I spent some time thinking
about it. I don't think this is really a global unique index, meaning
it is not one index with all the value in the index. Rather it is the
enforcement of uniqueness across all of a partitioned table's indexes.
I think global indexes have a limited enough use-case that this patch's
approach is as close as we are going to get to it in the foreseeable
future.
Second, I outlined the three values of global indexes in this blog
entry, based on a 2019 email thread:
https://momjian.us/main/blogs/pgblog/2020.html#July_1_2020
https://www.postgresql.org/message-id/[email protected]...
The three values are:
1. The ability to reference partitioned tables as foreign keys
without requiring the partition key to be part of the foreign
key reference; Postgres 12 allows such foreign keys if they match
partition keys.
2. The ability to add a uniqueness constraint to a partitioned
table where the unique columns are not part of the partition key.
3. The ability to index values that only appear in a few
partitions, and are not part of the partition key.
This patch should help with #1 and #2, but not #3. The uniqueness
guarantee allows, on average, half of the partitioned table's indexes to
be checked if there is a match, and all partitioned table's indexes if
not. This is because once you find a match, you don't need to keep
checking because the value is unique.
Looking at the patch, I am unclear how the the patch prevents concurrent
duplicate value insertion during the partitioned index checking. I am
actually not sure how that can be done without locking all indexes or
inserting placeholder entries in all indexes. (Yeah, that sounds bad,
unless I am missing something.)
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Embrace your flaws. They make you human, rather than perfect,
which you will never be.
view thread (33+ 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]
Subject: Re: Patch: Global Unique Index
In-Reply-To: <Y4D/[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