loadata/flush issues given GenericRelation, model inheritance and postgres

[pragmar]

This issue was encountered while porting from mysql to postgres and resembles #11107, but under a entirely different set of circumstances. There appears to be something going wrong in the database operations with the combination of model inheritance and generic relations. I've boiled it down to the following example using postgres 8.4.2 and psycopg2.

models.py

from django.db import models
from django.contrib.contenttypes.models import ContentType
from django.contrib.contenttypes import generic

class ExampleBase(models.Model):        
    title = models.CharField('title', max_length=200, blank=True, null=True)    
    created = models.DateTimeField('created', auto_now_add=True)
    modified = models.DateTimeField('modified', auto_now=True)        
    def __unicode__(self):
        return u'%s' % self.title    

class Event(ExampleBase):    
    start = models.DateTimeField('start time')
    end = models.DateTimeField('end')    
    content_type = models.ForeignKey(ContentType)
    object_id = models.PositiveIntegerField()    
    content_object = generic.GenericForeignKey("content_type", "object_id")
    
class Location(ExampleBase):
    address = models.CharField('address', max_length=200)
    city = models.CharField('city', max_length=50)
    state = models.CharField('state', max_length=50)    
    events = generic.GenericRelation(Event)

Given those models the error can be created by (1) running manage.py syncdb and (2) then running manage.py flush:

W:\development\web\example.com\example>manage.py flush
    Type 'yes' to continue, or 'no' to cancel: yes
Error: Database example couldn't be flushed. Possible reasons:
  * The database isn't running or isn't configured correctly.
  * At least one of the expected database tables doesn't exist.
  * The SQL was invalid.
Hint: Look at the output of 'django-admin.py sqlflush'. That's the SQL this command wasn't able to run.
The full error: relation "exampleapp_event_id_seq" does not exist
LINE 1: SELECT setval('"exampleapp_event_id_seq"', 1, false);

An identical error can be produced by via loaddata, here is a simple fixture for the above models, no generic relation objects need be created to trigger the error so long as the relationship is defined in the models:

[
  {
    "pk": 1, 
    "model": "exampleapp.examplebase", 
    "fields": {
      "created": "2010-01-29 08:27:10", 
      "modified": "2010-01-29 08:27:10", 
      "title": "test"
    }
  }, 
  {
    "pk": 1, 
    "model": "exampleapp.location", 
    "fields": {
      "city": "Boston", 
      "state": "MA", 
      "address": "1 Main St."
    }
  }
]

W:\development\web\example.com\example>manage.py loaddata test_fixture.json
Installing json fixture 'test_fixture' from absolute path.
Traceback (most recent call last):
  File "W:\development\web\example.com\example\manage.py", line 21, in <module>
    execute_manager(settings)
  File "C:\Python26\lib\site-packages\django\core\management\__init__.py", line 438, in execute_manager
    utility.execute()
  File "C:\Python26\lib\site-packages\django\core\management\__init__.py", line 379, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Python26\lib\site-packages\django\core\management\base.py", line 195, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "C:\Python26\lib\site-packages\django\core\management\base.py", line 222, in execute
    output = self.handle(*args, **options)
  File "C:\Python26\lib\site-packages\django\core\management\commands\loaddata.py", line 212, in handle
    cursor.execute(line)
psycopg2.ProgrammingError: column "id" does not exist
LINE 1: ... setval('"exampleapp_event_id_seq"', coalesce(max("id"), 1),...

I threw a print statement on the output of db.backends.postgresql.operations to see the full line (below):

['SELECT setval(\'"exampleapp_examplebase_id_seq"\', coalesce(max("id"), 1), max("id") IS NOT null) FROM "exampleapp_examplebase";',
 'SELECT setval(\'"exampleapp_event_id_seq"\', coalesce(max("id"), 1), max("id") IS NOT null) FROM "exampleapp_event";']

Let me know if there's more information I can provide. The same model hierarchy was working under mysql, and if I change the genericrelation to inherit from models.Model it works - so the issue requires a specific set of circumstances.

Thanks.

[pragmar]

So I don't want to pretend I have a solution, obviously sniffing for a class name feels very wrong - but I throw this out there as a possible assist to someone more familiar than myself with the database backend - just trying to give due diligence to the ticket and point to where I think it's failing. I'm able to work around the issue with a few tweaks. Given current trunk (r12344):

django/db/backends/postgresql/operations.py (line 125)
if not f.rel.through:

• becomes -

if not f.rel.through and not f.__class__.__name__ == 'GenericRelation':

additionally to fix flush, if I manually add the postgres sequence, it seems to work - perhaps the above modification omitted its creation?

[pragmar]

workaround to achieve successful loaddata given GenericRelation and abstract class.

[Russell Keith-Magee]

Not critical for 1.2

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

[Lorin Hochstein]

Note: This bug causes an ​issue in Mezzanine, a Django-based CMS.

[Lorin Hochstein]

It looks like ​the code in question assumes that the table has a column called "id", the query is hard-coded with 'id' in it:

                if not f.rel.through:
                    output.append("%s setval(pg_get_serial_sequence('%s','%s'), coalesce(max(%s), 1), max(%s) %s null) %s %s;" % \
                        (style.SQL_KEYWORD('SELECT'),
                        style.SQL_TABLE(qn(f.m2m_db_table())),
                        style.SQL_FIELD('id'),
                        style.SQL_FIELD(qn('id')),
                        style.SQL_FIELD(qn('id')),
                        style.SQL_KEYWORD('IS NOT'),
                        style.SQL_KEYWORD('FROM'),
                        style.SQL_TABLE(qn(f.m2m_db_table()))))

However, if the model uses multi-table inheritance, the table won't have an 'id' field.

[Lorin Hochstein]

Submitted a pull request here: ​https://github.com/django/django/pull/238

[Simon Charette]

Can you add a failing testcase in fixtures_regress.tests?

I'm also asking myself if this could be related to #7565 somehow. See ​fixtures_regress.tests.TestFixtures.test_pg_sequence_resetting_checks

[Lorin Hochstein]

I added a failing test case by ​modifying one of the existing test models so that the backends.SequenceResetTest will now fail unless the patch is applied.

[Lorin Hochstein]

My original pull request was against the wrong branch in the django repository. This new pull request is against the master branch: ​https://github.com/django/django/pull/408

[Tim Graham]

Patch applies cleanly and tests with postgresql_psycopg2 backend pass.

[Tim Graham]

I could be mistaken, but it looks like the regression test doesn't seem to fail without the patch applied anymore (the patch also needs to be updated for the move of tests/regressiontests/backends/models.py to tests/backends/models.py).

[Tim Graham]

Confirmed the new test no longer fails as of 97774429aeb54df4c09895c07cd1b09e70201f7d (#19385). I didn't investigate this further to see if the underlying issue is fixed or if this is still an issue and just needs a new test.

[jtbandes@…]

I believe it stopped being an issue because GenericRel is no longer a subclass of ManyToManyRel:

​https://github.com/django/django/blob/1.5/django/contrib/contenttypes/generic.py#L370
​https://github.com/django/django/blob/1.5/django/db/models/options.py#L174
vs.
​https://github.com/django/django/blob/1.6b1/django/contrib/contenttypes/generic.py#L376
​https://github.com/django/django/blob/1.6b1/django/db/models/fields/related.py#L827

[Tim Graham]

Ok, I will mark it as fixed.