Postgres error with syncdb and flush.
J. Cliff Dyer
Ubuntu 9.04
Python 2.6.2
Django 1.0.2
Postgres 8.3.7
postgresql-psycopg2
I'm having issues using ./manage.py syncdb and ./manage.py flush with a
project of mine. I've got an app, venues, which defines a
ManyToManyField.
After syncdb loads the fixtures, (and only if it tries to load fixtures)
it hits the following error:
{{{
[...]
Installing json fixture 'initial_data' from
'/cgi/django/apps/cdla_apps/venues/fixtures'.
Traceback (most recent call last):
File "./manage.py", line 11, in <module>
execute_manager(settings)
File
"/var/lib/python-support/python2.6/django/core/management/__init__.py",
line 340, in execute_manager
utility.execute()
File
"/var/lib/python-support/python2.6/django/core/management/__init__.py",
line 295, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File
"/var/lib/python-support/python2.6/django/core/management/base.py", line
192, in run_from_argv
self.execute(*args, **options.__dict__)
File
"/var/lib/python-support/python2.6/django/core/management/base.py", line
219, in execute
output = self.handle(*args, **options)
File
"/var/lib/python-support/python2.6/django/core/management/base.py", line
348, in handle
return self.handle_noargs(**options)
File
"/var/lib/python-support/python2.6/django/core/management/commands/syncdb.py", line 152, in handle_noargs
call_command('loaddata', 'initial_data', verbosity=verbosity)
File
"/var/lib/python-support/python2.6/django/core/management/__init__.py",
line 158, in call_command
return klass.execute(*args, **options)
File
"/var/lib/python-support/python2.6/django/core/management/base.py", line
219, in execute
output = self.handle(*args, **options)
File
"/var/lib/python-support/python2.6/django/core/management/commands/loaddata.py", line 161, in handle
cursor.execute(line)
File "/var/lib/python-support/python2.6/django/db/backends/util.py",
line 19, in execute
return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: column "id" does not exist
LINE 1: ...LECT setval('"venue_member_id_seq"', coalesce(max("id"),
1),...
}}}
The problem seems to be that VenueMember names its pk "id", but gives it
a db_column='venue_member_id' argument, as follows:
{{{
class VenueMember(_Model):
id = models.AutoField(db_column='venue_member_id', primary_key=True)
venue = models.ForeignKey(Venue)
member = models.ForeignKey(Member)
role = models.ForeignKey(Role, null=True, blank=True)
dates = models.ManyToManyField(Date, blank=True, null=True)
}}}
This model is referenced in my Venue model in a
ManyToManyField(through='VenueMember'):
{{{
class Venue(_Model):
# [... snip fields ...]
member_set = models.ManyToManyField('Member',
through='VenueMember',
blank=True)
}}}
When the database gets created, it creates a sequence named
'venue_member_venue_member_id_seq' on a column named 'venue_member_id',
but syncdb wants to access it via 'venue_member_id_seq', using the
column name 'id'.
./manage.py flush yields the following similar error:
{{{
Error: Database docsouth_gtts 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 "venue_member_id_seq" does not exist
}}}
Am I doing something wrong, or should I report a bug?
Cheers,
Cliff
J. Cliff Dyer
This seems to be closely related to ticket #10881
(db.backends.postgresql.operations.sequence_reset_sql and M2M fields
with non-integer PKs). In fact, the bug can be seen in the code shown
on the ticket description, though it's a different bug.
My initial thought was to make the loop beginning at line 123
(http://code.djangoproject.com/browser/django/trunk/django/db/backends/postgresql/operations.py#L123) smarter by changing:
style.SQL_FIELD(qn('%s_id_seq' % f.m2m_db_table()))
to:
style.SQL_FIELD(qn('%s_%s_id_seq' % (f.m2m_db_table(),
f.rel.through_model._meta.pk.column)))
and so on, but it occurs to me that if through=MyModel is set on the
ManyToMany, the sequence on MyModel will already be handled by the loop
at line 112. I think I can code up a patch that will fix #10881 and my
problem above in one fell swoop, with the caveat that in order for a
ManyToManyField to function properly with a non-integer primary key, it
will have to be defined as a model, and passed in explicitly with the
through=MyModel syntax.
Patch forthcoming.
J. Cliff Dyer
I've attached a patch to #10881 that fixes the reported problem and also
fixes mine for ./manage.py dbsync operations, but not for ./manage.py
flush; I still get the same error.
Cheers,
Cliff
J. Cliff Dyer
> On Wed, 2009-05-13 at 14:40 -0400, J. Cliff Dyer wrote:
> > [...] but it occurs to me that if through=MyModel is set on the
> > ManyToMany, the sequence on MyModel will already be handled by the loop
> > at line 112. I think I can code up a patch that will fix #10881 and my
> > problem above in one fell swoop, with the caveat that in order for a
> > ManyToManyField to function properly with a non-integer primary key, it
> > will have to be defined as a model, and passed in explicitly with the
> > through=MyModel syntax.
> >
> > Patch forthcoming.
> >
>
> I've attached a patch to #10881 that fixes the reported problem and also
> fixes mine for ./manage.py dbsync operations, but not for ./manage.py
> flush; I still get the same error.
>
> Cheers,
> Cliff
Since the patch seemed to be creeping beyond the scope of the reported
bug, I created a new ticket: #11107, and attached a patch to it which
solves the problem in all its manifestations, at least for postgres. I
can't try it on other databases. I'm new to django development and I
haven't gotten the test suite to run, so I haven't attached any tests.
If somebody has a chance to implement tests, that would be amazing, but
in the meantime, I'll try to figure out the test framework, and get it
running.
In the meantime, could somebody take a look at the patch, and let me
know if it needs any improvements?
Cheers,
Cliff
J. Cliff Dyer
The patch on #11107 (many_to_many.rel.through.4.diff) is all in order
with appropriate tests included.
The bug was also reported for oracle, so I added the same fix to the
oracle backend, but I don't have access to an oracle db, so I can't try
it out.
The tests are in regressiontests/m2m_through_regress, and they pass for
postgresql and postgresql_psycopg2 (and fail appropriately without the
the rest of the patch). Can someone test it on oracle? Assuming that
works, I think it's ready to go.
Cheers,
Cliff