django migrations to PostgreSQL not handling order_with_respect_to removals

[AJ]

If you change the Meta fields on a model to remove an order_with_respect_to tag, it creates a command in the new migration file like thus:

        migrations.AlterOrderWithRespectTo(
            name='entry',
            order_with_respect_to=None,
        ),

and everything works hunky-dory for SQLite. However... push your new code and migration files to a PostgreSQL server, like Heroku, run your migration file and ...

    ERROR 2017-11-22 16:51:18,081 exception 8 140423074580224 Internal Server Error:
    DETAIL:  Failing row contains (276, , 2017-11-22, U, 9, 1, null, ).

This little friend starts filling your log files. What does it mean? Well, a little exploration of the database tables show that the second last field that django is trying to assign null value to is the _order field.

_order field I'm presuming is only used by the order_with_respect_to tag. It doesn't seem to be created when an order = [ ' score ' ] command is used.

So it seems somewhere in the process it realises the _order field isn't needed, is assigning a null value to that column which clashes with a no null constraint. All when it should have simply deleted the _order field column after the migration was run.

An incompatibility bug between django and PostgreSQL perhaps?

Insights anyone?

[Tim Graham]

Hi, I can't reproduce the problem. Can you provide a minimal sample project that demonstrates the issue?

[Simon Charette]

Is it possible that you encountered the PostgreSQL constraint violation for the short period of time where your new code that removed the order_with_respect_to was deployed to your web workers while you had not run your migration that removed the _order column?

The only way to achieve zero-downtime for this kind of operation would be to perform the following manipulations:

1. Run a migration that makes _order nullable.
2. Deploy the code removing the order_with_respect_to to your workers.
3. Run a migration that drops the _order column.

It's really not different from the manipulations required for removing a normal non-nullable field. The AlterOrderWithRespectTo operation is harder to reason about because it doesn't make it clear it's implemented using an hidden _order field that requires the same care.

[AJ]

So I have to admit I have little understanding how Django builds migration scripts under the hood. I only work above the hood, but I can point you to the commits that track the problem.

My repo: ​https://bitbucket.org/talkingtoaj/lang/commits/all

The commit that introduced the problem: 21:21 21 Nov 2017 sha: 1a52c96
The reversal-commit that fixed the problem: 21:30 22 Nov 2017 sha: ffa6207
A second attempt that again re-introduced the problem: 21:42 22 Nov 2017 sha: de45121

I hope that sheds some light to where the problem lies. My guess is, migration 0010 of commit 1a52c96 while setting order_with_respect_to=None, failed to delete the _order field from the model's DB table.

Is it possible that since my local development testbed uses SQLite that the migration file was insufficiently built to allow for my live PostgreSQL DB? Or are the migration files definitely still DB-type agnostic?

[Simon Charette]

Is it possible that since my local development testbed uses SQLite that the migration file was insufficiently built to allow for my live PostgreSQL DB? Or are the migration files definitely still DB-type agnostic?

Migrations should be database agnostic but you should definitely be using the same database for your local environment and run your test suite against it.

So I have to admit I have little understanding how Django builds migration scripts under the hood. I only work above the hood, but I can point you to the commits that track the problem...

I'm afraid this ticket tracker isn't the right place to theorize about what could have gone wrong from your source as it would be very time consuming. Unless you're able are to provide steps to reproduce your issue or dismiss the theory described above related to your deployment strategy in regards to addition of non null-fields if afraid we'll have to close this ticket as 'needsinfo'.