PostgreSQL: django.db.utils.DatabaseError: column "id" does not exist

[Lorin Hochstein]

I'm working on the configuration for our production deployment, which will use PostgreSQL for the backend. In development, we've been using sqlite. I'm getting some errors when reading our database fixtures into PostgreSQL.

What I did was:

python manage.py createdb --noinput

python manage.py migrate --noinput

python manage.py loaddata ...

Here's the error I see when trying to load in a fixture corresponding to a mezzanine blog entry

Loading 10_blog.json ...

Traceback (most recent call last):

  File "./manage.py", line 36, in <module>

    execute_manager(settings)

  File "/home/deploy/venv/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 459, in execute_manager

    utility.execute()

  File "/home/deploy/venv/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 382, in execute

    self.fetch_command(subcommand).run_from_argv(self.argv)

  File "/home/deploy/venv/local/lib/python2.7/site-packages/django/core/management/base.py", line 196, in run_from_argv

    self.execute(*args, **options.__dict__)

  File "/home/deploy/venv/local/lib/python2.7/site-packages/django/core/management/base.py", line 232, in execute

    output = self.handle(*args, **options)

  File "/home/deploy/venv/local/lib/python2.7/site-packages/django/core/management/commands/loaddata.py", line 252, in handle

    cursor.execute(line)

  File "/home/deploy/venv/local/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 52, in execute

    return self.cursor.execute(query, args)

django.db.utils.DatabaseError: column "id" does not exist

LINE 1: ...e('"generic_threadedcomment"','id'), coalesce(max("id"), 1),...

I can reproduce the problem with the fixture like this:

[

  {

    "pk": 1,

    "model": "blog.blogpost",

    "fields": {

      "status": 2,

      "expiry_date": null,

      "allow_comments": true,

      "description": "Description goes here",

      "title": "title goes here",

      "rating_average": 0.0,

      "categories": [],

      "site": 1,

      "keywords_string": "",

      "content": "<p>content goes here</p>",

      "rating_count": 0,

      "comments_count": 0,

      "user": 1,

      "featured_image": "",

      "short_url": null,

      "publish_date": "2012-04-06T00:00:00",

      "slug": "blog-slug-here",

      "gen_description": true

    }

  }

]

Anybody have an idea about what could be going wrong here?

Lorin

[Brian Schott]

We're testing the theory that the initial migration for generic.ThreadedComment is missing the "id" autofield, which gets created by sqlite3 regardless, but not postgres. We think we might have to add this to all of the migrations, but none of my experiences with in the past sqlalchemy/south have been happy ones. Any thoughts?

diff --git a/mezzanine/generic/migrations/0001_initial.py b/mezzanine/generic/migrations/0001_initial.py
index 286627b..4c684b7 100644
--- a/mezzanine/generic/migrations/0001_initial.py
+++ b/mezzanine/generic/migrations/0001_initial.py
@@ -82,6 +82,7 @@ class Migration(SchemaMigration):
'by_author': ('django.db.models.fields.BooleanField', [], {'default': 'False', 'blank': 'True'}),
'comment_ptr': ('django.db.models.fields.related.OneToOneField', [], {'to': "orm['comments.Comment']", 'unique': 'True', 'primary_key': 'True'}),
'email_hash': ('django.db.models.fields.CharField', [], {'max_length': '100', 'blank': 'True'}),
+ 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
'replied_to': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'comments'", 'null': 'True', 'to': "orm['generic.ThreadedComment']"})
},
'sites.site': {

[Stephen McDonald]

The createdb command will run syncdb across all tables, then fake migrations, so I don't think the migrations will be at fault here.

--
Stephen McDonald
http://jupo.org

[Lorin Hochstein]

Yeah, changing that didn't fix things. The ThreadedComment model uses multi-table inheritance, so its primary key field is not called "id", it has a name that references the parent class. I don't know why postgresql assumes that the primary key field should be "id" in whatever SQL it generates to read in the blog fixtures.

Take care,

Lorin

--

Lorin Hochstein

Lead Architect - Cloud Services

Nimbis Services, Inc.

www.nimbisservices.com

[Stephen McDonald]

I can't for the life of me find the relevant section in the Django docs, but the solution might involve using the "natural keys" feature for fixtures.

[Nate Aune]

I'm getting the same error going from sqlite to postgresql. Lorin - did you ever figure out a workaround?

[Lorin Hochstein]

Nate:

I ended up patching Django to fix the problem and we are using a custom version of 1.4.1, which you can find here: 

https://github.com/nimbis/django (branch: 1.4.1-patched) 

I submitted a pull request to the Django project with a fix <https://github.com/django/django/pull/238> but I submitted it to the wrong upstream branch, it's on my todo list to resubmit it to the master branch.

Take care,

Lorin

--

Lorin Hochstein

Lead Architect - Cloud Services

Nimbis Services, Inc.

www.nimbisservices.com