Feedback-ID: ie3de48e3:Fastmail
Date: Mon, 20 Jul 2026 18:10:10 +0200
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
To: Dag Lem <dag@nimrod.no>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: REINDEX (CONCURRENTLY) TABLE handles DEFERRED constraints as
 IMMEDIATE while processing
Message-ID: <al5B8W-7OHMLlCLf@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <cc0470801d4ee46bd85f94c2516fd31b@nimrod.no>
Archived-At: 
 <https://www.postgresql.org/message-id/al5B8W-7OHMLlCLf%40alvherre.pgsql>
Precedence: bulk

On 2026-Jun-05, Dag Lem wrote:

> While processing, "REINDEX (CONCURRENTLY) TABLE table_name" temporarily
> treats the DEFERRED constraints as IMMEDIATE, causing transactions to fail
> with errors on the form 'ERROR:  duplicate key value violates unique
> constraint "uq_constraint_name"'.

I would say that this is clearly an oversight.

> Note how it is currently not possible to safely add a UNIQUE
> DEFERRED constraint following the example in
> https://www.postgresql.org/docs/18/sql-altertable.html
> 
> CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
> ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
>     ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX
> dist_id_temp_idx;
> 
> For this to work safely with UNIQUE DEFERRED constraints, I assume it would
> be necessary to add an option to CREATE INDEX to make an index DEFERRED.

I think this closely related problem is different.  We'd probably want
to fix the above in a backpatchable manner, but this one sounds like a
new feature.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Linux transformó mi computadora, de una `máquina para hacer cosas',
en un aparato realmente entretenido, sobre el cual cada día aprendo
algo nuevo" (Jaime Salinas)