Date: Sun, 2 Nov 2025 15:21:05 -0800
From: Noah Misch <noah@leadboat.com>
To: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Issues with ON CONFLICT UPDATE and REINDEX CONCURRENTLY
Message-ID: <20251102232105.a3@rfd.leadboat.com>
References: 
 <CANtu0ogv+6wqRzPK241jik4U95s1pW3MCZ3rX5ZqbFdUysz7Qw@mail.gmail.com>
 <CANtu0oj1J+bAhrfLfmiToXmNvSCw9uKKBYJLha47DPJA1eWexQ@mail.gmail.com>
 <CANtu0ojswSmzq7NHj6seOBkcxXHe5oxdZGGgku7zc1_ueHN+2g@mail.gmail.com>
 <CANtu0ohAo7Cgu4q8FsvX6=oWEz3BOoovxeRoAJWu=kFpeKd71w@mail.gmail.com>
 <CANtu0ohStYQkJL+_=40Odcqu2A4QPrM_VmofwkkudHGHFgymkA@mail.gmail.com>
 <CADzfLwWmxz0W+_iquiiBTP66ZFx5zhe1EGojurrtSop6sXrR=A@mail.gmail.com>
 <CADzfLwWtK9R3GvEp7ANfO3pE3dbpD3T99f9aGFMwV4HSerHE5w@mail.gmail.com>
 <CADzfLwWo+FV9WSeOah9F1r=4haa6eay1hNvYYy_WfziJeK+aLQ@mail.gmail.com>
 <20251027180606.f4.nmisch@google.com>
 <CADzfLwW4-uz8DQn0q5J3EUDaS3bEj2UyKX_CDY_qKos5ozSHjw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="huDzM1hR5qFLbvXI"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CADzfLwW4-uz8DQn0q5J3EUDaS3bEj2UyKX_CDY_qKos5ozSHjw@mail.gmail.com>
User-Agent: Mutt/2.2.12 (2023-09-09)
Archived-At: 
 <https://www.postgresql.org/message-id/20251102232105.a3%40rfd.leadboat.com>
Precedence: bulk


--huDzM1hR5qFLbvXI
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On Tue, Oct 28, 2025 at 02:19:00AM +0100, Mihail Nikalayeu wrote:
> On Mon, Oct 27, 2025 at 7:06 PM Noah Misch <noah@leadboat.com> wrote:
> > Yes, I'm happy to push a patch documenting it.  Would you like to propose the
> > specific doc patch?  I regret lacking the bandwidth to review the fix patches.
> 
> Of course!
> 
> First version in attachment, waiting for your comment.

Thanks.  Does "ON CONFLICT ON CONSTRAINT constraint_name" avoid the problem w/
concurrent REINDEX CONCURRENTLY?  A search of the thread found no mention of
"ON CONSTRAINT".  It seems safe to assume that clause would avoid problems w/
CREATE INDEX CONCURRENTLY, but that's less certain for REINDEX.

The attached version has these changes:

- Mention ON CONSTRAINT as a workaround.  Will remove if you find or suspect
  it's not effective.
- Limit the doc change to ON CONFLICT.  I think mentioning it at the INDEX
  commands is undue emphasis.
- Use term "unique violation", a term used earlier on the same page, instead
  of "duplicate key ...".
- Remove the internals-focused point about the brief window.
- Remove some detail I considered insufficiently surprising, e.g. the point
  about "compatible with the index being built".

--huDzM1hR5qFLbvXI
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename="doc-concurrently-on-conflict-v2nm.patch"

From: Noah Misch <noah@leadboat.com>

Doc: cover index CONCURRENTLY causing errors in INSERT ... ON CONFLICT.

Author: Mikhail Nikalayeu <mihailnikalayeu@gmail.com>
Reviewed-by: Noah Misch <noah@leadboat.com>
Discussion: https://postgr.es/m/CANtu0ojXmqjmEzp-=aJSxjsdE76iAsRgHBoK0QtYHimb_mEfsg@mail.gmail.com
Backpatch-through: 13

diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 3f13991..f88ea78 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -594,6 +594,17 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
     </para>
    </tip>
 
+   <warning>
+    <para>
+      While <command>CREATE INDEX CONCURRENTLY</command> or <command>REINDEX
+      CONCURRENTLY</command> is running on a unique index, <command>INSERT
+      ... ON CONFLICT</command> statements on the same table may unexpectedly
+      fail with a unique violation.  Using <literal>ON CONFLICT ON
+      CONSTRAINT</literal> <replaceable class="parameter">
+      constraint_name</replaceable> avoids this, by disabling inference.
+    </para>
+   </warning>
+
   </refsect2>
  </refsect1>
 

--huDzM1hR5qFLbvXI--