Thread: doc: Bring mention of unique index forced transaction wait behavior outside of the internal section
doc: Bring mention of unique index forced transaction wait behavior outside of the internal section
From
"David G. Johnston"
Date:
Hi.
The fact that one transaction will wait on another if they are trying to claim the same unique value is presently relegated to a subchapter of the documentation where the typical reader will not even understand (rightly so) the main chapter's title. This has prompted a number of questions being sent to the mailing list over the years about a topic we do cover in some detail in the documentation, most recently here:
Attached is a proposal for incorporating some high-level detail within the MVCC Chapter, where readers are already looking to learn about how transactions interact with each other and are "isolated" (or not, in this case) from each other.
I'm neither married to the verbiage nor location but it seems better than nothing and a modest improvement for not much effort. It's basically a glorified cross-reference. I didn't dislike directing the reader to the internals section enough to try and establish a better location for the main content. It just needs better navigation to it from places the reader is expected to peruse.
David J.
Attachment
Re: doc: Bring mention of unique index forced transaction wait behavior outside of the internal section
From
Aleksander Alekseev
Date:
Hi David, > It's basically a glorified cross-reference. I didn't dislike directing the reader to the internals section enough to tryand establish a better location for the main content. One problem I see is that: + [..], but as there is no pre-existing data, visibility checks are unnecessary. ... allows a wide variety of interpretations, most of which will be wrong. And all in all I find an added paragraph somewhat cryptic. If the goal is to add a cross-reference I suggest keeping it short, something like "For additional details on various corner cases please see ...". -- Best regards, Aleksander Alekseev
Re: doc: Bring mention of unique index forced transaction wait behavior outside of the internal section
From
"David G. Johnston"
Date:
On Tue, Jun 21, 2022 at 6:49 AM Aleksander Alekseev <aleksander@timescale.com> wrote:
Hi David,
> It's basically a glorified cross-reference. I didn't dislike directing the reader to the internals section enough to try and establish a better location for the main content.
One problem I see is that:
+ [..], but as there is no pre-existing data, visibility checks are unnecessary.
... allows a wide variety of interpretations, most of which will be
wrong. And all in all I find an added paragraph somewhat cryptic.
Yeah, I'd probably have to say "but since no existing record is being modified, visibility checks are unnecessary".
Is there a specific mis-interpretation that first came to mind for you that I can consider specifically?
If the goal is to add a cross-reference I suggest keeping it short,
something like "For additional details on various corner cases please
see ...".
That does work, and I may end up there, but it feels unsatisfying to be so vague/general.
David J.
Re: doc: Bring mention of unique index forced transaction wait behavior outside of the internal section
From
Bruce Momjian
Date:
On Tue, Jun 21, 2022 at 09:07:42AM -0700, David G. Johnston wrote: > On Tue, Jun 21, 2022 at 6:49 AM Aleksander Alekseev <aleksander@timescale.com> > wrote: > > Hi David, > > > It's basically a glorified cross-reference. I didn't dislike directing > the reader to the internals section enough to try and establish a better > location for the main content. > > One problem I see is that: > > + [..], but as there is no pre-existing data, visibility checks are > unnecessary. > > ... allows a wide variety of interpretations, most of which will be > wrong. And all in all I find an added paragraph somewhat cryptic. > > Yeah, I'd probably have to say "but since no existing record is being modified, > visibility checks are unnecessary". > > Is there a specific mis-interpretation that first came to mind for you that I > can consider specifically? > > > If the goal is to add a cross-reference I suggest keeping it short, > something like "For additional details on various corner cases please > see ...". > > That does work, and I may end up there, but it feels unsatisfying to be so > vague/general. I was not happy with putting this in the Transaction Isolation section. I rewrote it and put it in the INSERT secion, right before ON CONFLICT; patch attached. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson
Attachment
Re: doc: Bring mention of unique index forced transaction wait behavior outside of the internal section
From
Aleksander Alekseev
Date:
Hi Bruce, > I was not happy with putting this in the Transaction Isolation section. > I rewrote it and put it in the INSERT secion, right before ON CONFLICT; > patch attached. Looks good. -- Best regards, Aleksander Alekseev
Re: doc: Bring mention of unique index forced transaction wait behavior outside of the internal section
From
Bruce Momjian
Date:
On Mon, Jul 11, 2022 at 05:22:41PM +0300, Aleksander Alekseev wrote: > Hi Bruce, > > > I was not happy with putting this in the Transaction Isolation section. > > I rewrote it and put it in the INSERT secion, right before ON CONFLICT; > > patch attached. > > Looks good. Applied to all supported PG versions. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson
Re: doc: Bring mention of unique index forced transaction wait behavior outside of the internal section
From
"David G. Johnston"
Date:
On Thu, Jul 14, 2022 at 12:18 PM Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Jul 11, 2022 at 05:22:41PM +0300, Aleksander Alekseev wrote:
> Hi Bruce,
>
> > I was not happy with putting this in the Transaction Isolation section.
> > I rewrote it and put it in the INSERT secion, right before ON CONFLICT;
> > patch attached.
>
> Looks good.
Applied to all supported PG versions.
Sorry for the delayed response on this but I'm not a fan. A comment of some form in transaction isolation seems to make sense (even if not my original thought...that patch got messed up a bit anyhow), and while having something in INSERT makes sense this doesn't seem precise enough.
Comments about locking and modifying rows doesn't make sense (the issue isn't relegated to ON CONFLICT, simple inserts will wait if they happen to choose the same key to insert).
I would also phrase it as simply "Tables with a unique index will..." and not even mention tables that lack a unique index - those don't really exist and inference of their behavior by contrast seems sufficient.
Sticking close to what you proposed then:
INSERT into tables with a unique index might block when concurrent sessions are inserting conflicting rows (i.e., have identical values for the unique index columns) or when there already exists a conflicting row which is in the process of being deleted. Details are covered in <xref linkend="index-unique-checks"/>.
I can modify my original patch to be shorter and more on-point for inclusion in the MVCC chapter if there is interest in having a pointer from there to index-unique-checks as well. I think such a note regarding concurrency on an index naturally fits into one of the main pages for learning about concurrency in PostgreSQL.
David J.