Fixtures: core.serializers.sort_dependencies sorts models in the wrong order for FK constraints

[Andrew Wrigley]

Hi all,

I'm trying to dump a database fixture, and load it again to run tests. When I run `python manage.py test`, I get:

 

IntegrityError: Problem installing fixtures: insert or update on table "publisher_journaldupextid" violates foreign key constraint "publisher_journaldup_journal_id_67a6a414_fk_publisher"
DETAIL:  Key (journal_id)=(153) is not present in table "publisher_journal".

 

On further inspection this is because django.core.serializers.sort_dependencies, which I'm using to create the fixture, is sorting models in the wrong order. Looking at the source of sort_dependencies, the only constraints on the ordering of models relate to natural keys. I'm not using natural keys, but my database has foreign key constraints so order of loading is still important.

 

Am I missing something about how to save and load models without violating FK constraints?

[Mike Dewhirst]

On 5/05/2018 1:19 AM, Andrew Wrigley wrote:
>
> Hi all,
>
>
> I'm trying to dump a database fixture, and load it again to run tests.
> When I run `python manage.py test`, I get:
>

You need to --exclude all models with data not used in any tests.
Considering that tests should be run with known data, if you excluded
all models you would be able to put test data in any/every model. You
probably don't want (in most cases) to fetch existing relationships via
fixtures. An exception to this is unchanging reference data for example
cities and postcodes or countries and currencies.

Here is how my dumpdata is configered for scripted creation of fixtures ...

python manage.py dumpdata --settings=%proj%.settings.%production%
--indent=2 --verbosity=0 --natural-primary --natural-foreign --all
--exclude=contenttypes --exclude=auth.permission
--exclude=admin.logentry --exclude=sessions.session
--exclude=billing.fee --exclude=company.address
--exclude=company.company --exclude=company.companyprofile
--exclude=company.division --exclude=company.phone
--exclude=company.relationship
... etc ...
--exclude=workplace.workplace > %app1%/fixtures/initial_data.json

I think the term 'natural keys' includes the integers Django 'naturally'
uses. Hence, natural-primary and natural-foreign are just whatever
Django chooses. Those options mean you won't copy the existing keys from
the dumped database.

> IntegrityError: Problem installing fixtures: insert or update on
> table "publisher_journaldupextid" violates foreign key constraint
> "publisher_journaldup_journal_id_67a6a414_fk_publisher"
> DETAIL:  Key (journal_id)=(153) is not present in table
> "publisher_journal".
>

Your journal_id 153 isn't yet in the table and that means you are using
existing keys instead of natural ones.

Not sure if this helps

> On further inspection this is because
> django.core.serializers.sort_dependencies, which I'm using to create
> the fixture, is sorting models in the wrong order. Looking at the
> source of sort_dependencies, the only constraints on the ordering of
> models relate to natural keys. I'm not using natural keys, but my
> database has foreign key constraints so order of loading is still
> important.
>
> Am I missing something about how to save and load models without
> violating FK constraints?
>

> --
> You received this message because you are subscribed to the Google
> Groups "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to django-users...@googlegroups.com
> <mailto:django-users...@googlegroups.com>.
> To post to this group, send email to django...@googlegroups.com
> <mailto:django...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/aa34a69f-e20c-4d4f-92a0-e44429e62c0c%40googlegroups.com
> <https://groups.google.com/d/msgid/django-users/aa34a69f-e20c-4d4f-92a0-e44429e62c0c%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.

[Andrew Wrigley]

Hi Mike, thanks for the reply. My understanding is --natural-X only works if `natural_key`, `get_by_natural_key` are manually implemented on the models. I think the process should work without doing that, but I'm not sure why django orders the models in such a way that they can't be deserialised without violating FK constraints. Maybe this is a bug? I could --exclude thing, or manually control the serialisation order, but I'm hoping there is a more obvious solution I'm missing.

> <mailto:django-users+unsub...@googlegroups.com>.

[Mike Dewhirst]

On 7/05/2018 4:48 AM, Andrew Wrigley wrote:
> Hi Mike, thanks for the reply. My understanding is --natural-X only
> works if `natural_key`, `get_by_natural_key` are manually implemented
> on the models. I think the process should work without doing that, but
> I'm not sure why django orders the models in such a way that they
> can't be deserialised without violating FK constraints. Maybe this is
> a bug? I could --exclude thing, or manually control the serialisation
> order, but I'm hoping there is a more obvious solution I'm missing.

Just read the docs on natural keys and I have to say it is all news to
me. None of my models have those methods. Just adding the --natural
options solved my (admittedly early) problems. Also, I excluded as many
models as possible except for those which contain reference data which
are essential to drive the software. I handle a lot of jurisdictional
differences. Excluding all the other models makes sense for me in a test
situation because setUp() and tearDown() populate and remove data
respectively. It means I get an empty test database except for my
reference data.

Mike

> > an email to django-users...@googlegroups.com <javascript:>
> > <mailto:django-users...@googlegroups.com <javascript:>>.

> > To post to this group, send email to django...@googlegroups.com

> <javascript:>
> > <mailto:django...@googlegroups.com <javascript:>>.

> > Visit this group at https://groups.google.com/group/django-users

> <https://groups.google.com/group/django-users>.

> > To view this discussion on the web visit
> >
> https://groups.google.com/d/msgid/django-users/aa34a69f-e20c-4d4f-92a0-e44429e62c0c%40googlegroups.com
> <https://groups.google.com/d/msgid/django-users/aa34a69f-e20c-4d4f-92a0-e44429e62c0c%40googlegroups.com>
>
> >

> <https://groups.google.com/d/msgid/django-users/aa34a69f-e20c-4d4f-92a0-e44429e62c0c%40googlegroups.com?utm_medium=email&utm_source=footer

> <https://groups.google.com/d/msgid/django-users/aa34a69f-e20c-4d4f-92a0-e44429e62c0c%40googlegroups.com?utm_medium=email&utm_source=footer>>.
>
> > For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
> --
> You received this message because you are subscribed to the Google
> Groups "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to django-users...@googlegroups.com

> <mailto:django-users...@googlegroups.com>.

> To post to this group, send email to django...@googlegroups.com
> <mailto:django...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/django-users/594e96a6-76cf-4448-99c8-8bf46503aa7d%40googlegroups.com
> <https://groups.google.com/d/msgid/django-users/594e96a6-76cf-4448-99c8-8bf46503aa7d%40googlegroups.com?utm_medium=email&utm_source=footer>.