Foreign key prevents DB flush with custom flag model

[hheimbuerger]

I recently upgraded to 0.15 for the new swappable flag model (I had previously forked waffle to make my own modifications to the model). However, this is not an upgrade/migration problem, but rather I experience this issue when creating my DB from scratch (in development).

When I run my unit tests, I get the following error message:

CommandError: Database test_myproj couldn't be flushed. Possible reasons:
  * The database isn't running or isn't configured correctly.
  * At least one of the expected database tables doesn't exist.
  * The SQL was invalid.
Hint: Look at the output of 'django-admin sqlflush'. That's the SQL this command wasn't able to run.
The full error: cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "waffle_flag_groups" references "auth_group".
HINT:  Truncate table "waffle_flag_groups" at the same time, or use TRUNCATE ... CASCADE.

I believe the problem is that despite defining my own flag model, the waffle migrations (here: 0001_initial) still create the legacy flag model, including the waffle_flag_groups table. That's in addition to my account_flag table and its corresponding m2m account_flag_groups table.

The SQL that django-admin sqlflush generates, includes my account_flag_groups table, but it does not include the (unused by created in the schema) waffle_flag_groups, so the error message is entirely correct.

What's the correct solution here? I'd like to avoid having to fork waffle once again, I was really happy to get back on the mainline.

[hheimbuerger]

For the sake of testing, I manually deleted waffle_flag_groups and waffle_flag_users from my test database, and that resolved the issue temporarily.

postgres@debian:~$ psql
psql (10.6 (Debian 10.6-1.pgdg80+1))
postgres=# \connect test_myproj
You are now connected to database "test_myproj" as user "postgres".
test_myproj=# drop table waffle_flag_groups;
test_myproj=# drop table waffle_flag_users;

Obviously, this is really messy, will break the next time the tests are run without --keepdb and I wouldn't even know how to share this with my coworkers.

[clintonb]

@hheimbuerger is your model a child of the existing model, or a completely new one? Looking at django-oscar as an example, that project relies on child models rather than completely new models. This is more easily supported than new models since the existing migrations can remain intact. Otherwise, we'd need to have conditional migrations, which is not something I am comfortable supporting without prior art.

[hheimbuerger]

@clintonb Not sure what you mean by "child". My custom flag model inherits from AbstractUserFlag, as suggested by the documentation.

Other than that, I don't really understand what you mean by "[child models are] more easily supported than new models since the existing migrations can remain intact". Are you saying I should have created a new migration that has a dependency on waffle.0003_xxx and then issues ALTER TABLE statements to rename and modify the waffle_flag table? Wouldn't that contradict the whole point of defining a custom flag model in the first place?

[hheimbuerger]

I've been trying to explicitly reproduce this, using a fresh Django project and Postgres 10.x. I haven't succceeded at getting the error message during a test run (I'm not quite sure when Django calls sqlflush as part of a test), but here are basic reproduction steps:

1. Create a new Django 1.11 project, let's say myproj.
2. Connect it to your local Postgres DB, e.g. with user dev.
3. Define a new custom flag model, let's say CustomFlag and set it up as documented.
4. Build your initial migration for the new flag model: python manage.py makemigrations.
5. Create your initial schema: python manage.py migrate.
6. Run sqlflush against it: python manage.py sqlflush | psql --host localhost --username=dev --password --echo-all myproj.

You'll see that it will have generated a TRUNCATE command for the table auth_group, but not for the table waffle_flag_groups (because the latter has been replaced with our custom model's m2m table), so Postgres will refuse to TRUNCATE the former.

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "waffle_flag_groups" references "auth_group".

The problem is that the waffle.0001_initial migration has created the table waffle_flag_groups, even though our own migration myproj.0001_initial has created the table customflag_flag_groups, which should really replace the former.

[hheimbuerger]

Okay, I finally found the missing link to making this occur in tests: this only happens when the test class derives from Django's LiveServerTestCase. Here's a minimal tests.py.

from django.test import LiveServerTestCase

class WaffleTest(LiveServerTestCase):
    def test_waffle(self):
        self.assertTrue(True)

Run with python manage.py test, against a PostgreSQL DB, and you get the following output:

(venv) henrik@debian:~/myproj$ python manage.py test
Creating test database for alias 'default'...
System check identified no issues (0 silenced).
.E
======================================================================
ERROR: test_waffle (myproj.tests.WaffleTest)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/test/testcases.py", line 216, in __call__
    self._post_teardown()
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/test/testcases.py", line 925, in _post_teardown
    self._fixture_teardown()
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/test/testcases.py", line 960, in _fixture_teardown
    inhibit_post_migrate=inhibit_post_migrate)
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 131, in call_command
    return command.execute(*args, **defaults)
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/core/management/commands/flush.py", line 82, in handle
    six.reraise(CommandError, CommandError(new_msg), sys.exc_info()[2])
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/core/management/commands/flush.py", line 72, in handle
    cursor.execute(sql)
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
CommandError: Database test_myproj couldn't be flushed. Possible reasons:
  * The database isn't running or isn't configured correctly.
  * At least one of the expected database tables doesn't exist.
  * The SQL was invalid.
Hint: Look at the output of 'django-admin sqlflush'. That's the SQL this command wasn't able to run.
The full error: cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "waffle_flag_groups" references "auth_group".
HINT:  Truncate table "waffle_flag_groups" at the same time, or use TRUNCATE ... CASCADE.

----------------------------------------------------------------------
Ran 1 test in 0.511s

FAILED (errors=1)
Destroying test database for alias 'default'...

[Killsoft1692]

The Same Issue: Project requirements: Django==2.1.5 django-waffle==0.15.1 psycopg2==2.7.7 pytz==2018.9 PostgreSQL 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit

[Killsoft1692]

Okay, I finally found the missing link to making this occur in tests: this only happens when the test class derives from Django's LiveServerTestCase. Here's a minimal tests.py.

from django.test import LiveServerTestCase

class WaffleTest(LiveServerTestCase):
    def test_waffle(self):
        self.assertTrue(True)

Run with python manage.py test, against a PostgreSQL DB, and you get the following output:

(venv) henrik@debian:~/myproj$ python manage.py test
Creating test database for alias 'default'...
System check identified no issues (0 silenced).
.E
======================================================================
ERROR: test_waffle (myproj.tests.WaffleTest)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/test/testcases.py", line 216, in __call__
    self._post_teardown()
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/test/testcases.py", line 925, in _post_teardown
    self._fixture_teardown()
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/test/testcases.py", line 960, in _fixture_teardown
    inhibit_post_migrate=inhibit_post_migrate)
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 131, in call_command
    return command.execute(*args, **defaults)
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/core/management/commands/flush.py", line 82, in handle
    six.reraise(CommandError, CommandError(new_msg), sys.exc_info()[2])
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/core/management/commands/flush.py", line 72, in handle
    cursor.execute(sql)
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/henrik/venv/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
CommandError: Database test_myproj couldn't be flushed. Possible reasons:
  * The database isn't running or isn't configured correctly.
  * At least one of the expected database tables doesn't exist.
  * The SQL was invalid.
Hint: Look at the output of 'django-admin sqlflush'. That's the SQL this command wasn't able to run.
The full error: cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "waffle_flag_groups" references "auth_group".
HINT:  Truncate table "waffle_flag_groups" at the same time, or use TRUNCATE ... CASCADE.

----------------------------------------------------------------------
Ran 1 test in 0.511s

FAILED (errors=1)
Destroying test database for alias 'default'...

When you run tests Django at firsts runs flush which runs truncate without cascade. PostgreSQL by default checks FK and it's not possible to remove an object which have references to it in another tables. To achieve this we should just use cascade delete . You can see more info here: https://code.djangoproject.com/ticket/21961 I tried this tool: https://pypi.org/project/django-postgres-delete-cascade/ and it works. So my suggestion is to use it.

[clintonb]

@hheimbuerger @Killsoft1692 apologies for the delay. Do either of you want to open a PR to fix this issue?

[hheimbuerger]

@Killsoft1692 Interesting! So is this something that could be integrated into django-waffle, or will all waffle users that use PostgreSQL and custom flag models have to enable django-postgres-delete-cascade?

In the latter case, could you please propose a documentation change to django-waffle (via PR)?

[Killsoft1692]

@Killsoft1692 Interesting! So is this something that could be integrated into django-waffle, or will all waffle users that use PostgreSQL and custom flag models have to enable django-postgres-delete-cascade?

In the latter case, could you please propose a documentation change to django-waffle (via PR)?

It's django related thing so i think this is not connected directly to django-waffle. Also i don't think that we need change documentation because we can use DROP DATABASE 'name'; CREATE DATABASE 'name'; via psql Maybe we can update documentation about testing when we use django-waffle's custom flag. What do you think about it?

[benrudolph]

Also hitting this, any other workarounds that don't involve changing the db engine?

[benrudolph]

Edit: Ended up monkey patching the TransactionTestCase so it worked in all scenarios:

def _fixture_teardown(self):
    # Allow TRUNCATE ... CASCADE and don't emit the post_migrate signal
    # when flushing only a subset of the apps
    for db_name in self._databases_names(include_mirrors=False):
        # Flush the database
        inhibit_post_migrate = (
            self.available_apps is not None
            or (  # Inhibit the post_migrate signal when using serialized
                # rollback to avoid trying to recreate the serialized data.
                self.serialized_rollback
                and hasattr(connections[db_name], "_test_serialized_contents")
            )
        )
        call_command(
            "flush",
            verbosity=0,
            interactive=False,
            database=db_name,
            reset_sequences=False,
            # In the real TransactionTestCase this is conditionally set to False.
            allow_cascade=True,
            inhibit_post_migrate=inhibit_post_migrate,
        )

TransactionTestCase._fixture_teardown = _fixture_teardown

[hheimbuerger]

Our solution was to add an SQL migration to one of our own apps. It removes the tables from the waffle app that should have never been created, but are still part of the waffle.0001_initial migration.

class Migration(migrations.Migration):
    """
    Migration for issue: cannot truncate a table referenced in a foreign key constraint: Table "waffle_flag_groups" references "auth_group".
    See more: https://github.com/django-waffle/django-waffle/issues/317
    """

    dependencies = [
        ('waffle', '__latest__'),
    ]

    operations = [
        migrations.RunSQL(
            "DROP TABLE IF EXISTS waffle_flag_groups"
        ),
        migrations.RunSQL(
            "DROP TABLE IF EXISTS waffle_flag_users"
        )
    ]

That resolves all issues, as far as I'm aware, because now the internal dependency graph of Django matches the foreign key relationships in the database again. So PostgreSQL won't complain over Django generating constraint-violating TRUNCATE queries.

[benrudolph]

nice that's really useful. thank you i'll give that a go

[electis]

Edit: Ended up monkey patching the TransactionTestCase so it worked in all scenarios:

TransactionTestCase.available_apps = {app_config.name for app_config in apps.get_app_configs()} also works

[banagale]

I have implemented a custom flag model via

class Flag(AbstractUserFlag):
    """Customizable version of waffle's Flag model"""
    ...

I also ran into this issue at the start of my custom management build command, which has a DB clearing method that is ~

        # Methodically flush the database
        django_tables = connection.introspection.django_table_names(only_existing=True, include_views=False)
        django_tables.append('django_migrations')
        tables = list(map(connection.ops.quote_name, django_tables))
        with connection.cursor() as cursor:
            if connection.vendor == 'postgresql':
                for tn in tables:
                    cursor.execute('DROP TABLE IF EXISTS {} CASCADE'.format(tn))

What I found was that three tables, waffle_flag, waffle_flag_groups, and waffle_flag_users do not get returned by django.db.connection.introspection.django_table_names

As a result, when I try to build, after completing this flush step and moving on to a migrate, having these tables in place produces a similar error:

psycopg2.errors.DuplicateTable: relation "waffle_flag" already exists

If I append:

                cursor.execute('DROP TABLE IF EXISTS waffle_flag CASCADE')
                cursor.execute('DROP TABLE IF EXISTS waffle_flag_groups CASCADE')
                cursor.execute('DROP TABLE IF EXISTS waffle_flag_users CASCADE')

Below the final for loop above, it does remove the tables and I can run my build script which manually flushes and migrates the database.

I can't say for sure, but it seems like waffle is not reporting its tables, perhaps only when importing from AbstractUserFlag.

I trouble shot this by inspecting the database and manually deleting the waffle_flag table only to have the above error returned for the other two.

[hugorodgerbrown]

Here in 2023 - thanks for the solution @hheimbuerger

[destrex271]

Is there any other fix for the same error in case of LiveServerTestCase in Django?

[hheimbuerger]

Is there any other fix for the same error in case of LiveServerTestCase in Django?

LiveServerTestCase is a subclass of TransactionTestCase, so all the same causes and solutions/workarounds mentioned above should also apply to LiveServerTestCase.

[ArinaKonstantinovaEmpireLife]

thanks in 2023 as well

[cleor41]

Same, I run into this when I create sample data for either developing or demoing and need to flush the database. Will give hheimbuerger's solution a shot.

EDIT: Worked for me in 2024 with Django 4.2.