Opened 2 months ago

Last modified 2 months ago

#31653 new Cleanup/optimization

Add PostgreSQL operations to add constraints via NOT VALID / VALIDATE CONSTRAINT

Reported by: Adam (Chainz) Johnson Owned by: nobody
Component: contrib.postgres Version: master
Severity: Normal Keywords:
Cc: Simon Charette Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Adam (Chainz) Johnson)

If you read the ALTER TABLE PostgreSQL docs ( https://www.postgresql.org/docs/12/sql-altertable.html ) for ADD CONSTRAINT, you'll see that it supports the NOT VALID option. This prevents the constraint from being checked against all existing data, although it does affect all inserted/updated rows. Adding a constraint this way does not exclusively lock the whole table to check it. To promote it to valid, the VALIDATE CONSTRAINT syntax can be used. This allows a non-exclusive lock on the table to validate all existing rows. This two step techinque allows constraints to be added to very active tables without locking out concurrent access and thus inflicting downtime.

I suggest django.contrib.postgres add custom operations for these commands, with appropriate documentation around using them in non-atomic migrations.

Change History (8)

comment:1 Changed 2 months ago by Simon Charette

Cc: Simon Charette added

How would the VALIDATE CONSTRAINT step be performed? Were you thinking of using deferred_sql for this purpose? If it's the case how would you deal with VALIDATE CONSTRAINT failures (e.g. violation of the added constraint) since that'll mark the migration as not applied but leave the constraint in place and thus crash on attempts to re-apply.

I could see us provide specialized contrib.postgres operations that use this syntax to facilitate this manipulation (e.g. AddContraint(valid=False) and ValidateConstraint(name)) but I'm afraid this will be hard to achieve by default without risking leaving the migration and database state out-of-sync if the VALIDATE CONSTRAINT fails.

comment:2 Changed 2 months ago by Adam (Chainz) Johnson

It doesn't need to be deferred, as I understand it can be done within a migration transaction. I was imagining SchemaEditor.add_constraint can be overridden to add the NOT VALID clause then execute the second VALIDATE CONSTRAINT.

comment:3 Changed 2 months ago by felixxm

Similar NOVALIDATE syntax is available on Oracle. However on both databases it has some caveats.

comment:4 Changed 2 months ago by Simon Charette

The documentation states

After that, a VALIDATE CONSTRAINT command can be issued to verify that existing rows satisfy the constraint. The validation step does not need to lock out concurrent updates, since it knows that other transactions will be enforcing the constraint for rows that they insert or update; only pre-existing rows need to be checked.

My interpretation is that in order for other transactions to see that the constraint was added in a READ COMMITTED scenario the ADD CONSTRAINT NOT VALID must be committed first; performing a ADD CONSTRAINT NOT VALID and VALIDATE CONSTRAINT in the same transaction should result in the same operations as ADD CONSTRAINT otherwise PostgreSQL would always do it by default?

I could be missing something as I didn't try it out locally but from my understanding this would need the following sequence of migrations to be generated to work properly.

class Migration(migrations.Migration):
    operations = [
        # BEGIN
        AddConstraint(Constraint(name='foo'), validate=False),
        # COMMIT
    ]

class Migration(migrations.Migration):
    operations = [
        # BEGIN
        ValidateConstraint('foo'),
        # COMMIT
    ]

Running the first migration would commit the ADD CONSTRAINT NOT VALID and allow other transactions to start enforcing it which is something the VALIDATE CONSTRAINT could assume in the following transaction.

Last edited 2 months ago by Simon Charette (previous) (diff)

comment:5 Changed 2 months ago by Adam (Chainz) Johnson

My interpretation is that in order for other transactions to see that the constraint was added in a READ COMMITTED scenario the ADD CONSTRAINT NOT VALID must be committed first; performing a ADD CONSTRAINT NOT VALID and VALIDATE CONSTRAINT in the same transaction should result in the same operations as ADD CONSTRAINT otherwise PostgreSQL would always do it by default?

Yes... the one time I've used NOT VALID was actually in an atomic = False migration so I didn't test it with atomic. I was probably too zealous reading the PostgreSQL docs which don't mention transactions.

I think custom django.contrib.postgres operations would be the way. They can document to use non-atomic migrations. Rather than AddConstraint(..., validate=False) I'd suggest AddConstraintNotValid(...) as a subclass of AddConstraint, and ValidateConstraint.

comment:6 Changed 2 months ago by Adam (Chainz) Johnson

Component: Migrationscontrib.postgres
Description: modified (diff)
Summary: PostgreSQL add constraints via NOT VALID / VALIDATE CONSTRAINTAdd PostgreSQL operations to add constraints via NOT VALID / VALIDATE CONSTRAINT

comment:7 Changed 2 months ago by Simon Charette

Triage Stage: UnreviewedAccepted

I think custom django.contrib.postgres operations would be the way. They can document to use non-atomic migrations. Rather than AddConstraint(..., validate=False) I'd suggest AddConstraintNotValid(...) as a subclass of AddConstraint, and ValidateConstraint.

I think that's the way to go as well. I'd suggest we document breaking the operations in two migrations instead of using atomic=False otherwise your database will be left in a inconsistent state if the ValidateConstraint operation fails.

comment:8 Changed 2 months ago by Adam (Chainz) Johnson

I'd suggest we document breaking the operations in two migrations instead of using atomic=False otherwise your database will be left in a inconsistent state if the ValidateConstraint operation fails.

Yes. I think MySQL has made me reach for atomic = False too much 😂

Note: See TracTickets for help on using tickets.
Back to Top