GeoDjango syncdb fails if using post_syncdb signal on postgis to access geometry columns

[petr.gorodechnyj@…]

I use geojango on Postgresql 8.4 with the help of postgis 1.5. When I create model with geo field and use it as is - that's ok. But in my case I need to do some serious stuff on DB level so I created couple of postgresql sql-functions. So I wrote sql and execute it in post_syncdb signal handler.

models.py:

from django.contrib.gis.db import models

class Shop(models.Model):
    ....
    point               = models.PointField(null=True, spatial_index=False)

management.py:

from django.db.models.signals import post_syncdb
def post_syncdb_task(sender, **kwargs):
    from django.db import connection
    cursor = connection.cursor()
    sql =   '''CREATE OR REPLACE FUNCTION functionname(integer, text) RETURNS double precision AS $$ 
                SELECT ST_distance_sphere(point, ST_GeomFromEWKT($2)) as d 
                FROM this_package_shop 
                WHERE this_package_shop.id = $1;
            $$ LANGUAGE SQL;'''
    cursor.execute(sql);
from this_package import models as this_models
post_syncdb.connect(post_syncdb_task, sender=this_models)

When I execute syncdb on non-existing table, e.g. in python manage.py test I get:

  File "/home/gorodechnyj/workspace/projectname/this_package/management.py", line 18, in post_syncdb_task
    cursor.execute(sql);
  File "/usr/lib/python2.7/dist-packages/django/db/backends/postgresql_psycopg2/base.py", line 44, in execute
    return self.cursor.execute(query, args)
django.db.utils.DatabaseError: column "point" does not exist
LINE 2:                 SELECT ST_distance_sphere(point, ST_GeomFrom...

And most peculiar thing: there's really no trace of field point at all! It is simply not being created.
If I disable my post_syncdb handler, error dissapears and field "point" is being created but not sql functions.
So no testing on code which use them can be performed.

[Aymeric Augustin]

Note that Django supports initial SQL data, which is exactly what you need: ​https://docs.djangoproject.com/en/1.3/howto/initial-data/#providing-initial-sql-data

This said, we should figure out why executing SQL in a post_syncdb signal handler fails. The transaction should be committed before the signal handler is executed, so your tables should be available.

I can't see in your report a proof that this problem is specific to GeoDjango. Does it also happen for non-GIS models?

[petr.gorodechnyj@…]

Initial data not work with postgresql functions.
Tables are really available but no PointField is created. Let's say I create model Shop in package shops

class Shop(models.Model):
    address             = models.CharField(max_length=200, null=True)
    objects             = models.GeoManager()
    point               = models.PointField(null=True, spatial_index=False)

Whenever I disable or enable post_syncdb handler and execute "python manage.py sql shops" I get:

CREATE TABLE "shops_shop" (
    "id" serial NOT NULL PRIMARY KEY,
    "address" varchar(200)
)
;

If I disable handler and execute "python manage.py syncdb" following sql table is being generated (go this sql from pgadmin3):

CREATE TABLE shops_shop
(
  id serial NOT NULL,
  address character varying(200),
  point geometry,
  CONSTRAINT shops_shop_pkey PRIMARY KEY (id),
  CONSTRAINT enforce_dims_point CHECK (st_ndims(point) = 2),
  CONSTRAINT enforce_geotype_point CHECK (geometrytype(point) = 'POINT'::text OR point IS NULL),
  CONSTRAINT enforce_srid_point CHECK (st_srid(point) = 4326)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE shops_shop OWNER TO gorodechnyj;

If I enable handler and change it in a way so no geometry-field dependent function is being created I get the same sql and "point" column is being created. However if I enable at least one such function I get described DatabaseError and the following sql:

CREATE TABLE shops_shop
(
  id serial NOT NULL,
  address character varying(200),
  CONSTRAINT shops_shop_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE shops_shop OWNER TO gorodechnyj;

So it looks like the problem is with GeoDjango or with Postgis backend or with Postgresql itself. At least it will be very good to know why it happens.

[anonymous]

Concerning the previous comment. I found out the reason of such a behavior. If you add geometry column to your model you expect it to be included in sql DDL code for table but instead django executes following sql command

SELECT AddGeometryColumn('shops_shop', 'point', 4326, 'POINT', 2);

This means that in the time post_syncdb is being executed no 'point' column exists in the database.

There are two possible workarounds:
1) Do as suggested in #7561 and move post_syncdb further in code
2) Do something with GeoDjango so it can create adequate DDL command (include geometry columns in it)

[Aymeric Augustin]

Indeed, it looks a lot like a duplicate of #7561.

[Bas Peschier]

Closing as a duplicate of #7561. Similar to #13826, this presents a use case of GeoDjango, which should be fixed with a solution for the syncdb behaviour.