#31653: PostgreSQL add constraints via NOT VALID / VALIDATE CONSTRAINT
-------------------------------------+-------------------------------------
Reporter: Adam (Chainz) | Owner: nobody
Johnson |
Type: | Status: new
Cleanup/optimization |
Component: Migrations | 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
-------------------------------------+-------------------------------------
Comment (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 set of migrations to be
generated to work properly.
{{{#!python
class Migration(migrations.Migration):
operations = [
AddConstraint(Constraint(name='foo'), validate=False),
]
class Migration(migrations.Migration):
operations = [
ValidateConstraint('foo'),
]
}}}
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/31653#comment:4>
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.1feddaa0298cfb7fcb371fff33486bb3%40djangoproject.com.
