Repository navigation
Migration 0156_alter_contentartifact_relative_path_and_more.py fails on a big deployment #8058
Description
Activity
- changed the title
[-]Migration 0156_alter_contentartifact_relative_path_and_more.py fails on a big installation[/-][+]Migration 0156_alter_contentartifact_relative_path_and_more.py fails on a big deployment[/+]on Sep 3, 2026 Root Cause Analysis
Migration
0156_alter_contentartifact_relative_path_and_more.pyruns fourALTER TABLE ... ALTER COLUMN ... TYPE relative_pathoperations inside a single atomic transaction. EachALTER TABLEtakes an ACCESS EXCLUSIVE lock — PostgreSQL's strongest lock, blocking all reads and writes on the target table.More critically, PostgreSQL must scan every row in the table to validate the domain's CHECK constraint (
'/' || VALUE || '/' !~ '[\n\r\s\t\?#]|(/\.{0,2}/)'). On a 300 GB stage DB this takes 40 minutes; on an 8.5 TB production DB it never finishes.The deadlock mechanism:
- Migration acquires ACCESS EXCLUSIVE on
core_contentartifactand begins scanning rows - Running workers (sync tasks etc.) already holding row-level locks on that table queue behind the migration's lock
- Those queued workers also hold locks on other tables that the migration subsequently needs (
core_distribution,core_publishedartifact,core_publishedmetadata) - Deadlock: migration → waiting for a lock held by a queued worker → queued worker → waiting for the ACCESS EXCLUSIVE held by the migration
- Migration acquires ACCESS EXCLUSIVE on
Proposed Fix: Three-Phase Schema Change
PostgreSQL provides a two-phase pattern specifically for large-table schema changes:
ADD CONSTRAINT ... NOT VALID— brief ACCESS EXCLUSIVE (catalog write only, no row scan)VALIDATE CONSTRAINT— takes onlySHARE UPDATE EXCLUSIVE(allows full concurrent reads AND writes), does the expensive row scan- Once a table-level CHECK constraint matching the domain's check is validated, a subsequent
ALTER COLUMN TYPEto that domain skips the row scan — it becomes a fast catalog-only operation.
Phase 1 — Restructured migration 0156 (
atomic=False)For each of the four columns, use
RunSQLto add the domain-equivalent CHECK constraint asNOT VALID(fast, brief lock) andSeparateDatabaseAndStateto update Django's ORM understanding without the slow DB operation. ReplaceAddIndexwithAddIndexConcurrentlyfor the SpGist index.class Migration(migrations.Migration): atomic = False operations = [ migrations.SeparateDatabaseAndState( database_operations=[ migrations.RunSQL( sql=""" ALTER TABLE core_contentartifact ADD CONSTRAINT contentartifact_relative_path_domain_check CHECK ('/' || relative_path || '/' !~ E'[\\n\\r\\s\\t\\?#]|(/\\.{0,2}/)') NOT VALID; """, reverse_sql="ALTER TABLE core_contentartifact DROP CONSTRAINT IF EXISTS contentartifact_relative_path_domain_check;", ) ], state_operations=[ migrations.AlterField( model_name='contentartifact', name='relative_path', field=pulpcore.app.models.fields.RelativePathField(), ) ], ), # ... same pattern for distribution.base_path, publishedartifact.relative_path, publishedmetadata.relative_path migrations.AddIndexConcurrently( model_name='distribution', index=django.contrib.postgres.indexes.SpGistIndex(..., name='core_distribution_base_path_text'), ), ]
Phase 2 — New
datarepair-8058management command + dispatchable taskA new management command (and dispatchable Pulp task, following the pattern of
repair_7272) that runsVALIDATE CONSTRAINTon all four tables. This holds onlySHARE UPDATE EXCLUSIVE— the running application can continue serving requests and syncing while it runs.pulpcore-manager datarepair 8058Or via the API:
POST /pulp/api/v3/datarepair/8058/Progress reporting via
ProgressReportper table.Phase 3 — New migration 0157
After the constraints are validated, the actual
ALTER COLUMN TYPEis now a fast catalog-only operation (PostgreSQL trusts the validated constraints and skips the row scan). This migration runs quickly and safely with a live application.Operator workflow for the upgrade
# 1. Deploy release containing restructured 0156 — runs automatically, fast # 2. While app is running, trigger constraint validation (no downtime): pulpcore-manager datarepair 8058 # 3. Deploy release containing migration 0157 — runs automatically, fastOperators on very large deployments can run step 2 well in advance of step 3, or schedule it during a low-traffic window for extra caution — but it is not required since
VALIDATE CONSTRAINTallows full concurrent operation.My understanding is that NOT VALID means the constraint gets applied to inserts/updates, but leaves existing rows alone (ie, they may have 'invalid' data) Am I correct here?
That would impact us in cases where a query expected only-one-return as possible, but might get more-than-one. I don't know that that is an actual problem - it's just a possibly impact I can think of.
The only issue with 2-then-3, then, is we have downstream users that skip A LOT of versions in one update. They have occasionally skipped completely over all the versions where such a datarepair was available, and then been snagged by the forced-migration in Step 3. NOTE that this isn't a problem with the approach - just that pulpcore has use-cases where we've bitten ourselves with it in the past, and need to think Really Hard about when/if Step3 can happen
Great questions @ggainey.
On NOT VALID and existing rows:
Yes,
NOT VALIDmeans the constraint is enforced on new inserts/updates but existing rows are not scanned. However, therelative_pathconstraint is a format check — it validates that a path doesn't contain whitespace,?,#, or./..segments. It has no effect on query result cardinality. No existing query that expects a single row would suddenly return more rows because of this constraint. The concern would apply to exclusion constraints or unique constraints, not a CHECK on a single column's format.On version-skipping users and Step 3:
Good news: Step 3 (the final
ALTER COLUMN TYPEmigration) is not needed and will not be shipped.During implementation we empirically verified on PostgreSQL 16.8 that
ALTER COLUMN TYPE text → relative_pathperforms a full row scan even when a pre-validated matching CHECK constraint already exists. PostgreSQL's shortcut for skipping the scan only applies toSET NOT NULL, not to constrained domain conversions. So Step 3 would reintroduce the same longACCESS EXCLUSIVElock we're trying to avoid — there is no way to make it fast.The good news is that Step 3 is also unnecessary. The
relative_pathdomain is defined astextwith a CHECK constraint. The validated table-level CHECK constraint added by the restructured migration 0156 enforces the exact same rule. The DB column staystext, Django's ORM state reflectsRelativePathFieldviaSeparateDatabaseAndState, and all writes are validated by the constraint. The integrity guarantee is fully in place without the domain type on the column.So the operator workflow simplifies to:
# 1. Deploy release containing restructured 0156 — runs automatically, fast, no downtime # 2. Optionally, while app is running, validate existing rows: pulpcore-manager datarepair 8058Step 2 is optional for deployments that trust their existing data (or know it was already valid). Users who skip versions will have the NOT VALID constraints sitting unvalidated — the application works correctly, new writes are enforced, and they can run the datarepair at any convenient time.
Version
3.117.0
Describe the bug
The 0156_alter_contentartifact_relative_path_and_more.py migration fails to finish running on our large deployment. The database gets into a deadlock and the migration process fails. This happens over and over again as our deployment tries to run the migrations again.
The production DB is 8.5TB in size.
In our stage instance this migration finished in 40 minutes. That DB is 300GB.