MIME-Version: 1.0
Date: Fri, 05 Jun 2026 13:25:52 +0200
From: Dag Lem <dag@nimrod.no>
To: pgsql-bugs@lists.postgresql.org
Subject: REINDEX (CONCURRENTLY) TABLE handles DEFERRED constraints as
 IMMEDIATE while processing
Message-ID: <cc0470801d4ee46bd85f94c2516fd31b@nimrod.no>
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/cc0470801d4ee46bd85f94c2516fd31b%40nimrod.no>
Precedence: bulk

Hi,

The following bug was detected on PostgreSQL 16.14, and I was able to 
reproduce it on

PostgreSQL 18.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 16.1.1 
20260501 (Red Hat 16.1.1-1), 64-bit

We have a database table with some 150 million rows, with several 
indexes and a couple of constraints on the form CONSTRAINT 
uq_constraint_name UNIQUE (...) DEFERRABLE INITIALLY DEFERRED.

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"'.

To see what could cause this issue, I repeatedly did the SELECT below 
while REINDEX (CONCURRENTLY) was working, while also doing transactions 
which depend on constraints being DEFERRED.

SELECT i.indexrelid, c.relname, i.indisunique, i.indimmediate, 
i.indisvalid, i.indisready
FROM pg_class c
JOIN pg_index i ON i.indexrelid = c.oid
WHERE c.relkind = 'i' AND c.relname LIKE '%_ccnew'
ORDER BY i.indexrelid;

This yields output on the form below.

  indexrelid |                  relname                  | indisunique | 
indimmediate | indisvalid | indisready
------------+-------------------------------------------+-------------+--------------+------------+------------
      124315 | ix_non_unique_index_1_ccnew               | f           | 
t            | f          | t
      124316 | ix_non_unique_index_2_ccnew               | f           | 
t            | f          | t
      ...
      124342 | pk_primary_key_ccnew                      | t           | 
t            | f          | t
      124343 | uq_unique_deferred_constraint_1_ccnew     | t           | 
t            | f          | f
      124344 | uq_unique_deferred_constraint_2_ccnew     | t           | 
t            | f          | f
      124345 | pg_toast_71419_index_ccnew                | t           | 
t            | f          | f

Note how the new indexes backing the DEFERRED constraints are marked 
with indimmediate = true(!), and that the progress can be followed by 
looking at the indisready column.

As soon as construction of the first index backing a UNIQUE DEFERRED is 
completed (indisready = true), transactions depending on DEFERRED 
constraints will start to fail, and will continue to do so until REINDEX 
(CONCURRENTLY) has completed.

Could this be an oversight wrt. how indexes and (deferred) constraints 
are related? 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.

Best regards,
Dag Lem