Re: REINDEX (CONCURRENTLY) TABLE handles DEFERRED constraints as IMMEDIATE while processing

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)