On Fri, 25 Nov 2022 at 20:03, David Zhang <david.zh...@highgo.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
