Opened 15 years ago

Closed 9 years ago

#12400 closed Bug (fixed)

column "X" named in key does not exist error when models.PointField used in unique_together

Reported by: monkut Owned by: Claude Paroz
Component: GIS Version: dev
Severity: Normal Keywords: 1.9
Cc: chazen@…, ckarrie@… Triage Stage: Ready for checkin
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by jbronn)

Hit the following error when trying to use a geometry together with Meta: unique_together.
Using posgresql/PostGIS

Model:

class Data(models.Model):
    X = models.PointField(null=True, blank=True)
    Y = models.IntegerField()
    class Meta:
        unique_together = ('X', 'Y')
      

Exception on trying to syncdb/run test:

Creating table mydata_data
Traceback (most recent call last):
  File "manage.py", line 11, in <module>
    execute_manager(settings)
  File "C:\Python26\lib\site-packages\django\core\management\__init__.py", line 362, in execute_manager
    utility.execute()
  File "C:\Python26\lib\site-packages\django\core\management\__init__.py", line 303, 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\test.py", line 23, in handle
    failures = test_runner(test_labels, verbosity=verbosity, interactive=interactive)
  File "C:\Python26\lib\site-packages\django\contrib\gis\tests\__init__.py", line 135, in run_tests
    create_test_spatial_db(verbosity=verbosity, autoclobber=not interactive)
  File "C:\Python26\lib\site-packages\django\contrib\gis\db\backend\postgis\creation.py", line 135, in create_test_spatial_db
    call_command('syncdb', verbosity=verbosity, interactive=interactive)
  File "C:\Python26\lib\site-packages\django\core\management\__init__.py", line 166, in call_command
    return klass.execute(*args, **defaults)
  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\base.py", line 351, in handle
    return self.handle_noargs(**options)
  File "C:\Python26\lib\site-packages\django\core\management\commands\syncdb.py", line 78, in handle_noargs
    cursor.execute(statement)
psycopg2.ProgrammingError: column "X" named in key does not exist

I checked the sql generated and it seems this problem occurs because the the geometry column is added *after* the initial table is created. And the UNIQUE restriction is applied on initial table creation.

Change History (19)

comment:1 by Russell Keith-Magee, 15 years ago

Component: UncategorizedGIS
Triage Stage: UnreviewedDesign decision needed

comment:2 by jbronn, 15 years ago

Description: modified (diff)
Owner: changed from nobody to jbronn

Unfortunately, the OGC specification requires that the geometry column is added via the AddGeometryColumn stored procedure after the table is defined. There are a few options here:

(1) Modify so that unique_together with geometry columns is created by an ALTER TABLE statement after AddGeometryColumn is called. I think this would require a good amount of re-plumbing in django.db.backends.creation.BaseDatabaseCreation.sql_create_model and possibly some flag on Field itself to indicate which fields are added outside the CREATE TABLE definition.

(2) Use PostGIS 1.5 and the geography column type (which is a normal db column type and put in CREATE TABLE statement, unlike the geometry type). In other words, X = models.PointField(geography=True, null=True, blank=True). This also requires Django 1.2 (or SVN), but you'd be able to use the constraint.

(3) Use another database backend, like MySQL or Oracle, that doesn't create geometries with the AddGeometryColumn stored procedure.

Obviously, (1) is a long-term fix, while (2) and (3) are workarounds for the moment. This is definitely a DDN ticket for the moment.

comment:3 by anonymous, 15 years ago

Cc: chazen@… added

comment:4 by Matt McClanahan, 14 years ago

Severity: Normal
Type: Bug

comment:5 by Aymeric Augustin, 13 years ago

UI/UX: unset

Change UI/UX from NULL to False.

comment:6 by Aymeric Augustin, 13 years ago

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:7 by Christian Karrié, 13 years ago

Cc: ckarrie@… added
Version: 1.1SVN

Same in Postgis 2.0

comment:8 by Aymeric Augustin, 12 years ago

Triage Stage: Design decision neededAccepted

If this isn't fixed, it should at least be documented.

comment:9 by Claude Paroz, 10 years ago

Has patch: set
Owner: changed from jbronn to Claude Paroz
Status: newassigned

comment:10 by Tim Graham, 10 years ago

Triage Stage: AcceptedReady for checkin

comment:11 by Claude Paroz <claude@…>, 10 years ago

Resolution: fixed
Status: assignedclosed

In 01ec127b:

Fixed #12400 -- Allowed geometry fields in unique_together

Thanks Tim Graham for the review.

comment:12 by Tim Graham, 10 years ago

Has patch: unset
Resolution: fixed
Status: closednew
Triage Stage: Ready for checkinAccepted

Crash on Oracle after this change:

Traceback (most recent call last):
  File "./runtests.py", line 434, in <module>
    options.debug_sql)
  File "./runtests.py", line 256, in django_tests
    extra_tests=extra_tests,
  File "/home/jenkins/workspace/django-oracle/database/oragis11/label/trusty/python/python2.7/django/test/runner.py", line 210, in run_tests
    old_config = self.setup_databases()
  File "/home/jenkins/workspace/django-oracle/database/oragis11/label/trusty/python/python2.7/django/test/runner.py", line 166, in setup_databases
    **kwargs
  File "/home/jenkins/workspace/django-oracle/database/oragis11/label/trusty/python/python2.7/django/test/runner.py", line 370, in setup_databases
    serialize=connection.settings_dict.get("TEST", {}).get("SERIALIZE", True),
  File "/home/jenkins/workspace/django-oracle/database/oragis11/label/trusty/python/python2.7/django/db/backends/base/creation.py", line 71, in create_test_db
    run_syncdb=True,
  File "/home/jenkins/workspace/django-oracle/database/oragis11/label/trusty/python/python2.7/django/core/management/__init__.py", line 118, in call_command
    return command.execute(*args, **defaults)
  File "/home/jenkins/workspace/django-oracle/database/oragis11/label/trusty/python/python2.7/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/home/jenkins/workspace/django-oracle/database/oragis11/label/trusty/python/python2.7/django/core/management/commands/migrate.py", line 168, in handle
    self.sync_apps(connection, executor.loader.unmigrated_apps)
  File "/home/jenkins/workspace/django-oracle/database/oragis11/label/trusty/python/python2.7/django/core/management/commands/migrate.py", line 288, in sync_apps
    cursor.execute(statement)
  File "/home/jenkins/workspace/django-oracle/database/oragis11/label/trusty/python/python2.7/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/jenkins/workspace/django-oracle/database/oragis11/label/trusty/python/python2.7/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/jenkins/workspace/django-oracle/database/oragis11/label/trusty/python/python2.7/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
  File "/home/jenkins/workspace/django-oracle/database/oragis11/label/trusty/python/python2.7/django/db/backends/oracle/base.py", line 478, in execute
    return self.cursor.execute(query, self._param_generator(params))
django.db.utils.DatabaseError: ORA-02329: column of datatype ADT cannot be unique or a primary key

comment:13 by Claude Paroz, 10 years ago

Does that mean that Oracle doesn't support unique indexes on geometry fields? If that's the case, we'll probably need a new database feature. Then the difficulty will be to conditionally create the index during tests. As usual, I'm not able to work on Oracle fixes, but I'm available to discuss/help.

More generally, being able to skip some model import or creation depending on the backend would be useful for other tests too (gis, postgres, etc.).

comment:14 by Tim Graham, 10 years ago

Keywords: 1.9 added

comment:15 by Jani Tiainen, 9 years ago

Yes, Oracle doesn't support unique index on geometry fields so this must be resolved so that models and tests are skipped on Oracle by some means.

comment:16 by Anssi Kääriäinen, 9 years ago

Could you make the unique index definition conditional on used database, and the skip the tests that require the index by normal means? Alternatively you can make the model definition conditional on db, but that requires conditional imports, too.

comment:17 by Tim Graham, 9 years ago

Triage Stage: AcceptedReady for checkin

Pull request looks good (pending some cosmetic edits).

comment:18 by Tim Graham <timograham@…>, 9 years ago

In 1b8d7eff:

Refs #12400 -- Added supports_geometry_field_unique_index GIS db feature.

comment:19 by Tim Graham, 9 years ago

Resolution: fixed
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top