Opened 6 years ago

Closed 6 years ago

#13940 closed (invalid)

The ManyToManyField raises IntegrityError when used with postgresql

Reported by: DataGreed Owned by: nobody
Component: Database layer (models, ORM) Version: 1.2
Severity: Keywords: postgresql,
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


Update Django 1.2 has broken my site.

Here's the affected model:

class Category(models.Model):
    parent = models.ManyToManyField("self", symmetrical = False, null = True, blank = True)
    name = models.CharField(u"Name",max_length = 500)
    alias = models.SlugField(u"Alias for URLs")     
    creation_vars  = models.ManyToManyField(CatalogueVariables, symmetrical = True, blank = True, null=True,related_name = "creation_vars", verbose_name=u"New card vars")
    filter_vars = models.ManyToManyField(CatalogueVariables, symmetrical = True, blank = True, null=True,verbose_name=u"Filter vars")
    exp_filter_vars = models.ManyToManyField(CatalogueVariables, symmetrical = True, blank = True, null=True,related_name = "exp_filter_vars", verbose_name=u"Extended filter vars")
    order = models.IntegerField(u"Ordering", null = True, blank = True)
    class Meta():
        verbose_name = u"Category"
        verbose_name_plural = u"Categories"

Here's the related model:

class CatalogueVariables(models.Model):
    title = models.CharField(u"Name", max_length = 100)    
    alias = models.SlugField(u"Alias max_length = 70)              
    status = models.PositiveIntegerField(u"Status", choices = VariableStatusConstants.CHOICES, default = VariableStatusConstants.ENABLED)

Here is the problem:

>>> from catalogue.models import Category as c
>>> a  = c.objects.get(pk=362)
>>> from catalogue.models import CatalogueVariables as v
>>> s = v.objects.get(pk=452)
>>> a.creation_vars.add(s)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/fields/", line 494, in add
    self._add_items(self.target_field_name, self.source_field_name, *objs)
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/fields/", line 574, in _add_items
    '%s_id' % target_field_name: obj_id,
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/", line 352, in create, using=self.db)
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/", line 435, in save
    self.save_base(using=using, force_insert=force_insert, force_update=force_update)
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/", line 535, in save_base
  File "/usr/local/lib/python2.6/dist-packages/django/db/", line 175, in commit_unless_managed
  File "/usr/local/lib/python2.6/dist-packages/django/db/backends/", line 32, in _commit
    return self.connection.commit()
IntegrityError: insert or update on table "catalogue_category_creation_vars" violates foreign key constraint "catalogue_category_creation_vars_category_id_fkey"
DETAIL:  Key (category_id)=(452) is not found in table "catalogue_category".

On django 1.0 everything worked well (and on 1.1, as i can remember)

The problem also exists in the admin site. But if I add ManyToMany records manually, via pgadmin3 for postgresql - everything works well, no errors are returned and everything is ok.

Change History (6)

comment:1 Changed 6 years ago by DataGreed

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

Seems like django mistakes one key for another in the middle table for Many-to-many relationship

comment:2 Changed 6 years ago by DataGreed

Component: UncategorizedDatabase layer (models, ORM)

comment:3 Changed 6 years ago by DataGreed

INSERT INTO "catalogue_category_creation_vars" ("category_id", "cataloguevariables_id") VALUES (452, 362)

That's what is committed! But values are visa-versa! Why? And btw, in SQLite the query is the same, but everything works normall. I am confused

comment:4 Changed 6 years ago by DataGreed

Wait a second, that's what is going on:

{'time': '0.001', 'sql': u'SELECT "catalogue_category_creation_vars"."cataloguevariables_id" FROM "catalogue_category_creation_vars" WHERE ("catalogue_category_creation_vars"."category_id" = 362  AND "catalogue_category_creation_vars"."cataloguevariables_id" IN (452))'}, 

{'time': '0.000', 'sql': u'INSERT INTO "catalogue_category_creation_vars" ("category_id", "cataloguevariables_id") VALUES (362, 452)'}, 

{'time': '0.001', 'sql': u'SELECT "catalogue_category_creation_vars"."category_id" FROM "catalogue_category_creation_vars" WHERE ("catalogue_category_creation_vars"."cataloguevariables_id" = 362  AND "catalogue_category_creation_vars"."category_id" IN (452))'}, 

{'time': '0.000', 'sql': u'INSERT INTO "catalogue_category_creation_vars" ("category_id", "cataloguevariables_id") VALUES (452, 362)'}

WHat are the two last queries are for?

comment:5 Changed 6 years ago by DataGreed

Whoops, forget it: just need to set symmetrical to false (btw, it's better to check this sort of things in the midlle DB layer)

comment:6 Changed 6 years ago by Ramiro Morales

Resolution: invalid
Status: newclosed

Closing because there is no clear explanation of what the Django problem is, if any. Please re-open if yo can describe concisely.

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