Bulk document creation/import triggers `impress_document_path_key` `IntegrityError`

[StephanMeijer]

Context

Manually bulk-importing multiple files at once against preprod (impress-staging.beta.numerique.gouv.fr) intermittently fails on POST /api/v1.0/documents/ with:

IntegrityError: duplicate key value violates unique constraint "impress_document_path_key"
DETAIL:  Key (path)=(00000L6) already exists.

• Sentry: DOCS-83B — 794 occurrences between 2026-06-15 and 2026-07-08 on release 5.3.0
• Reproduction: bulk-importing several files at once from the client fires concurrent POST /api/v1.0/documents/ calls that race on materialized-path root computation

Root cause

create_tree_node_with_retry in src/backend/core/utils/treebeard.py:39 — introduced in a47c3519 to replace table locks with a retry strategy — wraps Document.add_root() and retries on path collisions. The retry works well for low-contention creates, but three things make it fragile under bursts:

1. No jitter in the backoff. time.sleep(attempt * 0.1) is deterministic. Concurrent workers that collide in the same tick all sleep the same duration and retry in lockstep, colliding again.

2. Small total sleep budget. Across the 10 default attempts, cumulative sleep is 0 + 0.1 + … + 0.9 = 4.5s. A burst wider than ~10 concurrent creators must produce at least one hard failure per contested round — only one worker can win the unique constraint per round.

3. treebeard's MP_AddRootHandler reads MAX(path) without a row lock. Under READ COMMITTED, concurrent transactions see the same max and compute the same next path. The unique index correctly catches duplicates, but retries only diverge if the winner's commit becomes visible to the readers — under sustained synchronized retries, they don't reliably diverge.

Reaching Sentry means all TREEBEARD_PATH_COMPUTE_RETRY_MAX_ATTEMPTS=10 retries were exhausted on the same collision.

Suggested fixes

Roughly in increasing effort:

1. Add jitter to the backoff in create_tree_node_with_retry (src/backend/core/utils/treebeard.py:60):

   time.sleep(random.uniform(0, (attempt + 1) * 0.1))

   Small change, breaks the lockstep retry pattern, usually drops the collision rate by an order of magnitude on its own.

2. Raise TREEBEARD_PATH_COMPUTE_RETRY_MAX_ATTEMPTS on preprod as an interim mitigation (already env-configurable — see src/backend/impress/settings.py:1114).

3. PostgreSQL advisory lock scoped to root-path computation — cheaper than the previous table lock and without the deadlock risk that motivated a47c3519:

   with transaction.atomic():
       cursor.execute("SELECT pg_advisory_xact_lock(%s)", [ADD_ROOT_LOCK_KEY])
       return create_fn()

   Serializes only the MAX(path) read+insert; no row locks held across the transaction.

Impact

Currently only reproduced on preprod during manual bulk-import testing. The same failure mode would apply to any legitimate bursty create workload in production — a future bulk-import feature, mass template instantiation, migrations from another tool, etc. — so worth fixing before it surfaces there.

---

Investigated and co-written by Claude Opus 4.7.