#22611 closed Bug (wontfix)

sqlclear command tries to drop non-existing constraint with ForeignKey to self (postgresql)

Reported by: valberg Owned by:
Component: Core (Management commands) Version: 1.7-beta-2
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


Having the following model (in an app called test_app):

class Foo(models.Model):
    bar = models.ForeignKey('self')

The sqlclear management command returns:

$ ./manage.py sqlclear testapp
ALTER TABLE "testapp_foo" DROP CONSTRAINT "bar_id_refs_id_83e148c5";
DROP TABLE "testapp_foo";


Piping this into psql results in the following error:

> ./manage.py sqlclear testapp | psql -U django -h localhost django
ERROR:  constraint "bar_id_refs_id_83e148c5" of relation "testapp_foo" does not exist
ERROR:  current transaction is aborted, commands ignored until end of transaction block

Checking the table in psql reveals:

django=# \d testapp_foo
                         Table "public.testapp_foo"
 Column |  Type   |                        Modifiers
 id     | integer | not null default nextval('testapp_foo_id_seq'::regclass)
 bar_id | integer | not null
    "testapp_foo_pkey" PRIMARY KEY, btree (id)
    "testapp_foo_bar_id" btree (bar_id)
Foreign-key constraints:
    "testapp_foo_bar_id_fkey" FOREIGN KEY (bar_id) REFERENCES testapp_foo(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "testapp_foo" CONSTRAINT "testapp_foo_bar_id_fkey" FOREIGN KEY (bar_id) REFERENCES testapp_foo(id) DEFERRABLE INITIALLY DEFERRED

So it seems that the sqlclear command generates a different name for the
constraint than it actually is. I'm guessing that postgresql is in charge of
naming the constraint. At least looking at the output of the sqlall command, there
is no indication of Django doing any naming.

$ ./manage.py sqlall testapp
CREATE TABLE "testapp_foo" (
    "id" serial NOT NULL PRIMARY KEY,
    "bar_id" integer NOT NULL REFERENCES "testapp_foo" ("id") DEFERRABLE INITIALLY DEFERRED
CREATE INDEX "testapp_foo_bar_id" ON "testapp_foo" ("bar_id");


I've figured out that the "wrongful" naming is done in
but there my knowledge of Djangos ORM stops :)

It might be worth noting that doing a simple DROP statement drops the table
just fine. So maybe the ALTER statement isn't necessary?:

$ echo 'BEGIN; DROP TABLE "testapp_foo"; COMMIT;' | psql -U django -h localhost django

(I'm using Django 1.7b3 installed with pip install git+https://github.com/django/django@stable/1.7.x#egg=Django, but it is not listed in the version list.)

Change History (11)

comment:1 Changed 19 months ago by claudep

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

I can confirm this bug, I've encountered it while working on #19750. However, the future of 'sql*' management commands is a bit uncertain with the new migration framework. At least, they will need to be updated to use the new DatabaseSchemaEditor classes.

comment:2 Changed 19 months ago by olethanh

  • Owner changed from nobody to olethanh
  • Status changed from new to assigned

comment:3 Changed 19 months ago by valberg

olethanh: How far are you with this?

comment:4 Changed 19 months ago by valberg

  • Owner changed from olethanh to valberg

comment:5 Changed 19 months ago by valberg

  • Has patch set
  • Patch needs improvement set

I've made a pull request (https://github.com/django/django/pull/2659) and would appreciate any feedback. I'm uncertain on a couple of topics:

  • The schema_editor.create_model() takes care of creating indexes, so sql_indexes() is not that straight forward to convert to using schema editor.
  • The old sql functions took the style argument, but this is not used anywhere in the schema editor.
  • The tests pass, but they probably need more improvement.
  • Currently it does not do any nice coloring/highlighting of the output. It would be nice, but is not that important in my opinion.
Last edited 19 months ago by valberg (previous) (diff)

comment:6 Changed 18 months ago by andrewgodwin

  • Severity changed from Normal to Release blocker

I'd actually like to drop the sql* commands for apps that are marked as having migrations, as they're no longer needed, and we'll finally deprecate and remove them along with other creation-based stuff in 2.0 (they should pretty much be replaced by sqlmigrate, which obviously stays)

In particular, I think that:

  • sql
  • sqlall
  • sqlcustom
  • sqlindexes
  • sqldropindexes
  • sqlsequencereset

should be modified to error and quit if supplied with an app that's got migrations. This would leave just sqlflush and sqlmigrate working for those apps, which both make sense in the context.

comment:7 Changed 18 months ago by valberg

I have made a new PR (now following the contribution guidelines):


There are still some issues with coloring though. I will address that if the rest of the PR looks good.

comment:8 Changed 18 months ago by andrewgodwin

  • Severity changed from Release blocker to Normal

valberg has just confirmed on IRC that this is also a problem on 1.6, so as it's no longer a regression, dropping release blocker status.

comment:9 Changed 18 months ago by valberg

  • Has patch unset
  • Owner valberg deleted
  • Patch needs improvement unset
  • Status changed from assigned to new

Since this is an old bug, and the sql commands are likely to go away in future versions due to migrations, I'm stopping the work on this.

comment:10 Changed 17 months ago by claudep

#22910 was a duplicate (related to sqldropindexes)

comment:11 Changed 11 months ago by timgraham

  • Resolution set to wontfix
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.
Back to Top