agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: Álvaro 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
Date: Mon, 20 Jul 2026 18:10:10 +0200
Message-ID: <al5B8W-7OHMLlCLf@alvherre.pgsql> (raw)
In-Reply-To: <cc0470801d4ee46bd85f94c2516fd31b@nimrod.no>

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)

view thread (4+ messages)  latest in thread

Message-ID: <al5B8W-7OHMLlCLf@alvherre.pgsql>
Permalink:  ../al5B8W-7OHMLlCLf@alvherre.pgsql/
Also on:    postgresql.org/message-id/al5B8W-7OHMLlCLf@alvherre.pgsql

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: alvherre@kurilemu.de, dag@nimrod.no, pgsql-bugs@lists.postgresql.org
  Subject: Re: REINDEX (CONCURRENTLY) TABLE handles DEFERRED constraints as IMMEDIATE while processing
  In-Reply-To: <al5B8W-7OHMLlCLf@alvherre.pgsql>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox