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

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