Opened 5 years ago
Last modified 4 years ago
#31653 closed New feature
PostgreSQL add constraints via NOT VALID / VALIDATE CONSTRAINT — at Initial Version
Reported by: | Adam Johnson | Owned by: | nobody |
---|---|---|---|
Component: | contrib.postgres | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Simon Charette | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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 always use this form when adding foreign keys and check constraints.