Code

Opened 3 years ago

Closed 3 years ago

#16160 closed New feature (duplicate)

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

Reported by: petr.gorodechnyj@… Owned by: nobody
Component: GIS Version: 1.3
Severity: Normal Keywords: geodjango postgresql post_syncdb
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Attachments (0)

Change History (5)

comment:1 Changed 3 years ago by aaugustin

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Summary changed from Geojango syncdb fails if using post_syncdb sygnal on postgis to Geojango syncdb fails if using post_syncdb signal on postgis

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?

comment:2 Changed 3 years ago by 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.

comment:3 Changed 3 years ago by anonymous

  • Keywords geodjango added; geojango removed
  • Summary changed from Geojango syncdb fails if using post_syncdb signal on postgis to GeoDjango syncdb fails if using post_syncdb signal on postgis to access geometry columns
  • Type changed from Bug to New feature

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)

comment:4 Changed 3 years ago by aaugustin

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

comment:5 Changed 3 years ago by bpeschier

  • Resolution set to duplicate
  • Status changed from new to closed
  • UI/UX unset

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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


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

 
Note: See TracTickets for help on using tickets.