Opened 2 years ago

Last modified 9 months ago

#20581 new New feature

Support DEFERRABLE INITIALLY DEFERRED for UNIQUE constraints

Reported by: dmadeley@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

It would be useful, when using unique_together, to be able to defer constraint checking the way we defer foreign key checking until the end of the transaction.

Change History (4)

comment:1 Changed 2 years ago by akaariai

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted
  • Type changed from Uncategorized to New feature
  • Version changed from 1.5 to master

Agreed that there are use cases where deferred unique constraints would be useful. However I am not sure if this needs to be part of Django's API (maybe it will be easy enough to create these constraints manually in the upcoming database migrations?).

This will be impossible to implement on those databases that do not support deferred unique constraints. I believe this includes both MySQL and SQLite of the core databases. I think this is a blocker for this ticket: if there is an API for unique deferred constraints in Django, then how does SQLite and MySQL interact with models that have these constraints?

I am marking this as accepted, but this should not be taken as indication that if patch is written it will be included in Django. This is accepted more in the sense that "yes, this seems useful in some cases, lets investigate if this should be part of Django".

comment:2 Changed 2 years ago by dmadeley@…

I was wondering if it could be added for UNIQUE in the backend when supported, the way the postgres backend currently does for foreign keys (i.e. always). Though INITALLY DEFERRED seems like something that more generically should be supported for foreign keys and other constraints via a deferred=True property on the field/meta and then a context manager for defer_constraints (which I think exists).

FWIW I did create the constraints I needed manually in my South migrations.

Probably a good first step is to break apart the create_model into more parts that the backend can override, and then adding DEFERRABLE to all postgres backend constraint adding methods (maybe after checking the version, since I think it requires a somewhat new postgres).

comment:3 Changed 2 years ago by akaariai

I don't think it is a good idea to use DEFERRABLE INITIALLY DEFERRED for unique constaints. This will break backwards compatibility. The moment when unique constraint violation is raised is important, code can rightfully expect that unique constraint violations are raised on the moment of insert/update, not on commit.

It seems even DEFERRABLE INITIALLY IMMEDIATE can have negative results "To obtain standard-compliant behavior, declare the constraint as DEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be significantly slower than immediate uniqueness checking." [www.postgresql.org/docs/9.0/static/sql-createtable.html]. So, creating all unique constraints as DEFERRABLE INITIALLY IMMEDIATE doesn't seem like a good option.

So, this needs some sort of API to inform that a deferrable constraint (initially immediate/not immediate) is needed. Maybe this could be achieved by having something like models.Unique(fields=('f1', 'f2'), deferrable=True) on the table (there might also be models.PrimaryKey(fields=('f1', 'f2')) at some point). Adding an API for unique_together seems complex, and adding a dedicated API just for deferred unique constraints doesn't feel right.

(FWIW I think foreign key constraints should be created as DEFERRABLE INITIALLY IMMEDIATE and switched to DEFERRED only when needed, eg for cascading deletes. It seems hard to actually get into this situation, this breaks existing code, and needs database migrations to work correctly).

I wonder if this should be marked "someday/maybe"...

comment:4 Changed 9 months ago by koniiiik

My idea for an API was also to use something like models.Unique(*fields, deferrable=True, initially_deferred=True), but not as a Model class attribute, but rather inside unique_together:

class OrderedItem(models.Model):
    owner = ForeignKey('auth.User')
    order = PositiveIntegerField()
    data = TextField()

    class Meta:
        unique_together = (
            models.Unique('owner', 'order', deferrable=True),
        )

The idea is similar to urlpatterns – those also accept either a tuple, or the result of a url call. Also, it doesn't add a new API for this kind of constraints, merely extends the existing one.

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