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

[Vidir Valberg Gudmundsson]

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
BEGIN;
ALTER TABLE "testapp_foo" DROP CONSTRAINT "bar_id_refs_id_83e148c5";
DROP TABLE "testapp_foo";

COMMIT;

Piping this into psql results in the following error:

> ./manage.py sqlclear testapp | psql -U django -h localhost django
BEGIN
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
ROLLBACK

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
Indexes:
    "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
BEGIN;
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");

COMMIT;

I've figured out that the "wrongful" naming is done in
django.db.backends.creation.BaseDatabaseCreation.sql_remove_table_constraints,
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
BEGIN
DROP TABLE
COMMIT

(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.)

[Claude Paroz]

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.

[Vidir Valberg Gudmundsson]

olethanh: How far are you with this?

[Vidir Valberg Gudmundsson]

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.

[Andrew Godwin]

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.

[Vidir Valberg Gudmundsson]

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

​https://github.com/django/django/pull/2742

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

[Andrew Godwin]

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.

[Vidir Valberg Gudmundsson]

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.

[Claude Paroz]

#22910 was a duplicate (related to sqldropindexes)