The ManyToManyField raises IntegrityError when used with postgresql

[DataGreed]

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/related.py", 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/related.py", line 574, in _add_items
    '%s_id' % target_field_name: obj_id,
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/query.py", line 352, in create
    obj.save(force_insert=True, using=self.db)
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/base.py", 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/base.py", line 535, in save_base
    transaction.commit_unless_managed(using=using)
  File "/usr/local/lib/python2.6/dist-packages/django/db/transaction.py", line 175, in commit_unless_managed
    connection._commit()
  File "/usr/local/lib/python2.6/dist-packages/django/db/backends/__init__.py", 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.

[DataGreed]

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

[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

[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?

[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)

[Ramiro Morales]

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