public inbox for [email protected]
help / color / mirror / Atom feedREINDEX (CONCURRENTLY) TABLE handles DEFERRED constraints as IMMEDIATE while processing
2+ messages / 1 participants
[nested] [flat]
* REINDEX (CONCURRENTLY) TABLE handles DEFERRED constraints as IMMEDIATE while processing
@ 2026-06-05 11:25 Dag Lem <[email protected]>
2026-06-05 12:35 ` Re: REINDEX (CONCURRENTLY) TABLE handles DEFERRED constraints as IMMEDIATE while processing Dag Lem <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Dag Lem @ 2026-06-05 11:25 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: REINDEX (CONCURRENTLY) TABLE handles DEFERRED constraints as IMMEDIATE while processing
2026-06-05 11:25 REINDEX (CONCURRENTLY) TABLE handles DEFERRED constraints as IMMEDIATE while processing Dag Lem <[email protected]>
@ 2026-06-05 12:35 ` Dag Lem <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Dag Lem @ 2026-06-05 12:35 UTC (permalink / raw)
To: [email protected]
On 2026-06-05 13:25, 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"'.
Apologies, this should be "constraint_name_ccnew" - the new index
backing the constraint.
Best regards,
Dag Lem
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-06-05 12:35 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-05 11:25 REINDEX (CONCURRENTLY) TABLE handles DEFERRED constraints as IMMEDIATE while processing Dag Lem <[email protected]>
2026-06-05 12:35 ` Dag Lem <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox