#31653: Add PostgreSQL operations to add constraints via NOT VALID / VALIDATE
CONSTRAINT
-------------------------------------+-------------------------------------
Reporter: Adam (Chainz) | Owner: nobody
Johnson |
Type: | Status: new
Cleanup/optimization |
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Adam (Chainz) Johnson):
* component: Migrations => contrib.postgres
Old 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.
New 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.contrib.postgres` add custom operations for these
commands, with appropriate documentation around using them in non-atomic
migrations.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/31653#comment:6>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/068.4f5f0f7323044715736a11b39285ea74%40djangoproject.com.
