Opened 5 years ago
Closed 4 years ago
#31653 closed New feature (fixed)
Add PostgreSQL operations to add constraints via NOT VALID / VALIDATE CONSTRAINT
Description (last modified by ) ¶
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 (18)
comment:1 by , 5 years ago
Cc: | added |
---|
comment:2 by , 5 years ago
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 by , 5 years ago
Similar NOVALIDATE
syntax is available on Oracle. However on both databases it has some caveats.
comment:4 by , 5 years ago
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.
comment:5 by , 5 years ago
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 by , 5 years ago
Component: | Migrations → contrib.postgres |
---|---|
Description: | modified (diff) |
Summary: | PostgreSQL add constraints via NOT VALID / VALIDATE CONSTRAINT → Add PostgreSQL operations to add constraints via NOT VALID / VALIDATE CONSTRAINT |
comment:7 by , 5 years ago
Triage Stage: | Unreviewed → Accepted |
---|
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 by , 5 years ago
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 😂
comment:9 by , 4 years ago
I'd like to work on this ticket. I was thinking of adding an add_constraint()
in db.backends.postgresql.schema.DatabaseSchemaEditor
, which could modify the sql returned by calling constraint.create_sql(model, self)
to have the NOT VALID
clause as well. This could be then used by the custom operations, that'll be defined in contrib.postgres.operations
. Does this work or is my approach missing something here?
comment:10 by , 4 years ago
I think it would be better to keep the SQL modification purely in the operation class. It's only for PostgreSQL, so it doesn't need to be part of the more generic backend class.
comment:11 by , 4 years ago
But wouldn't modifying the SQL in a new add_constraint()
method in db.backends.postgresql.schema.DatabaseSchemaEditor
avoid making it backend agnostic and make it Postgres specific?
comment:12 by , 4 years ago
The backend classes exist mostly as a unified API across all supported backends. When I see a method in a backend class, I presume it exists with the same signature in all other backend classes.
comment:13 by , 4 years ago
This form adds a new constraint to a table using the same constraint syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints.
We should probably also document the fact that this operation only supports CheckConstraint
s and NOT ForeignKey
s. I think it'd be better if we added Postgres operations that support the NOT VALID
clause for fk checks as well in the future.
comment:15 by , 4 years ago
Owner: | changed from | to
---|---|
Patch needs improvement: | set |
Status: | new → assigned |
Type: | Cleanup/optimization → New feature |
comment:16 by , 4 years ago
Patch needs improvement: | unset |
---|
comment:17 by , 4 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
How would the
VALIDATE CONSTRAINT
step be performed? Were you thinking of usingdeferred_sql
for this purpose? If it's the case how would you deal withVALIDATE 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)
andValidateConstraint(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 theVALIDATE CONSTRAINT
fails.