Re: doc: Bring mention of unique index forced transaction wait behavior outside of the internal section

On Thu, Jul 14, 2022 at 12:18 PM Bruce Momjian <bruce(at)momjian(dot)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.