Code

Opened 5 years ago

Last modified 16 months ago

#12400 new Bug

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

Reported by: monkut Owned by: jbronn
Component: GIS Version: master
Severity: Normal Keywords:
Cc: chazen@…, ckarrie@… Triage Stage: Accepted
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.

Attachments (0)

Change History (8)

comment:1 Changed 4 years ago by russellm

  • Component changed from Uncategorized to GIS
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Design decision needed

comment:2 Changed 4 years ago by jbronn

  • 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 Changed 4 years ago by anonymous

  • Cc chazen@… added

comment:4 Changed 3 years ago by mattmcc

  • Severity set to Normal
  • Type set to Bug

comment:5 Changed 2 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:6 Changed 2 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:7 Changed 2 years ago by ckarrie

  • Cc ckarrie@… added
  • Version changed from 1.1 to SVN

Same in Postgis 2.0

comment:8 Changed 16 months ago by aaugustin

  • Triage Stage changed from Design decision needed to Accepted

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from jbronn to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.