public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dag Lem <[email protected]>
To: [email protected]
Subject: REINDEX (CONCURRENTLY) TABLE handles DEFERRED constraints as IMMEDIATE while processing
Date: Fri, 05 Jun 2026 13:25:52 +0200
Message-ID: <[email protected]> (raw)

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

view thread (2+ messages)  latest in thread

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: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: REINDEX (CONCURRENTLY) TABLE handles DEFERRED constraints as IMMEDIATE while processing
  In-Reply-To: <[email protected]>

* 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