Date: Mon, 28 Nov 2022 16:28:55 -0500
From: Bruce Momjian <bruce@momjian.us>
To: David Zhang <david.zhang@highgo.ca>
Cc: Simon Riggs <simon.riggs@enterprisedb.com>,
	Cary Huang <cary.huang@highgo.ca>,
	Pgsql Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Patch: Global Unique Index
Message-ID: <Y4UoF7JTlquWQ+V9@momjian.us>
References: <184879c5306.12490ea581628934.7312528450011769010@highgo.ca>
 <CANbhV-Eo4D2Njpa=YiBr7BxOMDi5B54wenjgoamRKjzj-9gBGQ@mail.gmail.com>
 <Y4D/3EbEibKkJW2C@momjian.us>
 <46a0f6fb-1c0b-bbd8-ef3e-366216a89501@highgo.ca>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <46a0f6fb-1c0b-bbd8-ef3e-366216a89501@highgo.ca>
Archived-At: 
 <https://www.postgresql.org/message-id/Y4UoF7JTlquWQ%2BV9%40momjian.us>
Precedence: bulk

On Fri, Nov 25, 2022 at 05:03:06PM -0800, David Zhang 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 can't see why this wouldn't work, but I also can't think of any cases
where we do this in our code already, so it will need careful
consideration.

We kind of do this for UPDATE and unique key conflicts, but only for a
single index entry. where we peek and sleep on pending changes, but not
across indexes.

> I am not quite sure if this is a proper way to deal with a deadlock in this
> case. It would be so grateful if someone could help provide some cases/methods
> to verify this cross all partitions uniqueness.

I assume you are using our existing deadlock detection code, and just
sleeping in various indexes and expecting deadlock detection to happen.

-- 
  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.