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 )
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 , 15 years ago
Component: | Uncategorized → GIS |
---|---|
Triage Stage: | Unreviewed → Design decision needed |
comment:2 by , 15 years ago
Description: | modified (diff) |
---|---|
Owner: | changed from | to
comment:3 by , 15 years ago
Cc: | added |
---|
comment:4 by , 14 years ago
Severity: | → Normal |
---|---|
Type: | → Bug |
comment:8 by , 12 years ago
Triage Stage: | Design decision needed → Accepted |
---|
If this isn't fixed, it should at least be documented.
comment:9 by , 10 years ago
Has patch: | set |
---|---|
Owner: | changed from | to
Status: | new → assigned |
comment:10 by , 10 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
comment:12 by , 10 years ago
Has patch: | unset |
---|---|
Resolution: | fixed |
Status: | closed → new |
Triage Stage: | Ready for checkin → Accepted |
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 , 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 , 10 years ago
Keywords: | 1.9 added |
---|
comment:15 by , 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 , 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 , 9 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
Pull request looks good (pending some cosmetic edits).
comment:19 by , 9 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
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 anALTER TABLE
statement afterAddGeometryColumn
is called. I think this would require a good amount of re-plumbing indjango.db.backends.creation.BaseDatabaseCreation.sql_create_model
and possibly some flag onField
itself to indicate which fields are added outside theCREATE TABLE
definition.(2) Use PostGIS 1.5 and the
geography
column type (which is a normal db column type and put inCREATE TABLE
statement, unlike thegeometry
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.