public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Greg Stark <[email protected]>
Cc: David Zhang <[email protected]>
Cc: Bruce Momjian <[email protected]>
Cc: Simon Riggs <[email protected]>
Cc: Cary Huang <[email protected]>
Cc: Pgsql Hackers <[email protected]>
Subject: Re: Patch: Global Unique Index
Date: Tue, 29 Nov 2022 18:13:56 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAM-w4HNFyX2upz6pyeWXew-p2sPXiSy2q3tn_Zfa4Xvtu+SLhw@mail.gmail.com>
References: <[email protected]>
	<CANbhV-Eo4D2Njpa=YiBr7BxOMDi5B54wenjgoamRKjzj-9gBGQ@mail.gmail.com>
	<Y4D/[email protected]>
	<[email protected]>
	<CAM-w4HNFyX2upz6pyeWXew-p2sPXiSy2q3tn_Zfa4Xvtu+SLhw@mail.gmail.com>

Greg Stark <[email protected]> writes:
> 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?

... not to mention creating a high probability of deadlocks between
concurrent insertions to different partitions.  If they each
ex-lock their own partition's index before starting to look into
other partitions' indexes, it seems like a certainty that such
cases would fail.  The rule of thumb about locking multiple objects
is that all comers had better do it in the same order, and this
isn't doing that.

That specific issue could perhaps be fixed by having everybody
examine all the indexes in the same order, inserting when you
come to your own partition's index and otherwise just checking
for conflicts.  But that still means serializing insertions
across all the partitions.  And the fact that you need to lock
all the partitions, or even just know what they all are, is
going to play hob with a lot of assumptions we've made about
different partitions being independent, and about what locks
are needed for operations like ALTER TABLE ATTACH PARTITION.

(I wonder BTW what the game plan is for attaching a partition
to a partitioned table having a global index.  Won't that mean
having to check every row in the new partition against every
one of the existing partitions?  So much for ATTACH being fast.)

I still think this is a dead end that will never get committed.
If folks want to put time into perhaps finding an ingenious
way around these problems, okay; but they'd better realize that
there's a high probability of failure, or at least coming out
with something nobody will want to use.

			regards, tom lane

view thread (33+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Patch: Global Unique Index
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox