Migration 0156_alter_contentartifact_relative_path_and_more.py fails on a big deployment

[dkliban]

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.

[dkliban]

Root Cause Analysis

Migration 0156_alter_contentartifact_relative_path_and_more.py runs four ALTER TABLE ... ALTER COLUMN ... TYPE relative_path operations inside a single atomic transaction. Each ALTER TABLE takes 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:

1. Migration acquires ACCESS EXCLUSIVE on core_contentartifact and begins scanning rows
2. Running workers (sync tasks etc.) already holding row-level locks on that table queue behind the migration's lock
3. Those queued workers also hold locks on other tables that the migration subsequently needs (core_distribution, core_publishedartifact, core_publishedmetadata)
4. Deadlock: migration → waiting for a lock held by a queued worker → queued worker → waiting for the ACCESS EXCLUSIVE held by the migration

[dkliban]

Proposed Fix: Three-Phase Schema Change

PostgreSQL provides a two-phase pattern specifically for large-table schema changes:

1. ADD CONSTRAINT ... NOT VALID — brief ACCESS EXCLUSIVE (catalog write only, no row scan)
2. VALIDATE CONSTRAINT — takes only SHARE UPDATE EXCLUSIVE (allows full concurrent reads AND writes), does the expensive row scan
3. Once a table-level CHECK constraint matching the domain's check is validated, a subsequent ALTER COLUMN TYPE to 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 RunSQL to add the domain-equivalent CHECK constraint as NOT VALID (fast, brief lock) and SeparateDatabaseAndState to update Django's ORM understanding without the slow DB operation. Replace AddIndex with AddIndexConcurrently for 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-8058 management command + dispatchable task

A new management command (and dispatchable Pulp task, following the pattern of repair_7272) that runs VALIDATE CONSTRAINT on all four tables. This holds only SHARE UPDATE EXCLUSIVE — the running application can continue serving requests and syncing while it runs.

pulpcore-manager datarepair 8058

Or via the API:

POST /pulp/api/v3/datarepair/8058/

Progress reporting via ProgressReport per table.

Phase 3 — New migration 0157

After the constraints are validated, the actual ALTER COLUMN TYPE is 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, fast

Operators 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 CONSTRAINT allows full concurrent operation.

[ggainey]

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

[dkliban]

Great questions @ggainey.

On NOT VALID and existing rows:

Yes, NOT VALID means the constraint is enforced on new inserts/updates but existing rows are not scanned. However, the relative_path constraint 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 TYPE migration) is not needed and will not be shipped.

During implementation we empirically verified on PostgreSQL 16.8 that ALTER COLUMN TYPE text → relative_path performs a full row scan even when a pre-validated matching CHECK constraint already exists. PostgreSQL's shortcut for skipping the scan only applies to SET NOT NULL, not to constrained domain conversions. So Step 3 would reintroduce the same long ACCESS EXCLUSIVE lock 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_path domain is defined as text with a CHECK constraint. The validated table-level CHECK constraint added by the restructured migration 0156 enforces the exact same rule. The DB column stays text, Django's ORM state reflects RelativePathField via SeparateDatabaseAndState, 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 8058

Step 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.

[dralley]

@dkliban FYI pulp/pulp-docs#204