Re: Patch: Global Unique Index - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: Patch: Global Unique Index
Date
Msg-id Y4D/3EbEibKkJW2C@momjian.us
Whole thread Raw
In response to Re: Patch: Global Unique Index  (Simon Riggs <simon.riggs@enterprisedb.com>)
Responses Re: Patch: Global Unique Index
List pgsql-hackers
On Mon, Nov 21, 2022 at 12:33:30PM +0000, Simon Riggs wrote:
> On Thu, 17 Nov 2022 at 22:01, Cary Huang <cary.huang@highgo.ca> 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
storagestructure 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/CA+Tgmob_J2M2+QKWrhg2NjQEkMEwZNTfd7a6Ubg34fJuZPkN2g@mail.gmail.com

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  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

Embrace your flaws.  They make you human, rather than perfect,
which you will never be.



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #17434: CREATE/DROP DATABASE can be executed in the same transaction with other commands
Next
From: Pavel Borisov
Date:
Subject: Re: Lockless queue of waiters in LWLock