Re: Patch: Global Unique Index

On Mon, Nov 21, 2022 at 12:33:30PM +0000, Simon Riggs wrote:
> On Thu, 17 Nov 2022 at 22:01, Cary Huang <cary(dot)huang(at)highgo(dot)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 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/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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

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