Re: Patch: Global Unique Index

On Fri, 25 Nov 2022 at 20:03, David Zhang <david(dot)zhang(at)highgo(dot)ca> wrote:
>
> Hi Bruce,
>
> Thank you for helping review the patches in such detail.
>
> On 2022-11-25 9:48 a.m., Bruce Momjian wrote:
>
> 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.)
>
> For the uniqueness check cross all partitions, we tried to follow the implementation of uniqueness check on a single partition, and added a loop to check uniqueness on other partitions after the index tuple has been inserted to current index partition but before this index tuple has been made visible. The uniqueness check will wait `XactLockTableWait` if there is a valid transaction in process, and performs the uniqueness check again after the in-process transaction finished.

I think this is the key issue to discuss. The rest is all UX
bikeshedding (which is pretty important in this case) but this is the
core uniqueness implementation.

If I understand correctly you're going to insert into the local index
for the partition using the normal btree uniqueness implementation.
Then while holding an exclusive lock on the index do lookups on every
partition for the new key. Effectively serializing inserts to the
table?

I think the precedent here are "exclusion constraints" which are
documented in two places in the manual:
https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION
https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

These also work by doing lookups for violating entries and don't
depend on any special index machinery like btree uniqueness. But I
don't think they need to entirely serialize inserts either so it may
be worth trying to figure out how they manage this to avoid imposing
that overhead.

There's a comment in src/backend/executor/execIndexing.c near the top
about them but I'm not sure it covers all the magic needed for them to
work...

--
greg