Migrating a model's default primary key to a BigAutoField causes Postgres sequence to lose owner

[Dolan Antenucci]

Summary of Issue

Start with the following models (below assumes application name is "sandbox"):

class Test1(models.Model):
    id = models.BigAutoField(primary_key=True)
    name = models.CharField(max_length=100)

class Test2(models.Model):
    name = models.CharField(max_length=100)

After migrating, go the the dbshell and run \d sandbox_test1_id_seq and \d sandbox_test2_id_seq. The results will include "Owned by: public.sandbox_test1.id" and "Owned by: public.sandbox_test2.id" respectively.

Next, change Test2 to a BigIntField:

class Test2(models.Model):
    id = models.BigAutoField(primary_key=True)
    name = models.CharField(max_length=100)

Make a new migration, migrate, and then go back to dbshell and run \d sandbox_test2_id_seq. There will no longer be an owner listed for this sequence.

Result of this issue

When using loaddata with fixtures on the Test2 model, the sequence will not be incremented because the command Django uses to reset the sequence for the primary key fails. Specifically, in the ​postgres backend, Django calls the postgres function pg_get_serial_sequence(<table>, <column>), which returns nothing when the sequence is missing an owner.

This can be verified in postgres shell via select pg_get_serial_sequence('sandbox_test1', 'id'); and select pg_get_serial_sequence('sandbox_test2', 'id');

The result is that after the fixture is loaded, any other inserts will fail because their primary keys will conflict with those in the fixture.

Potential fixes

1. It seems like makemigrations should be setting the sequence owner, or using a different command to migrate so that the sequence owner information is not lost. For example, the ​postgres docs on serial columns show that this can be done with ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
2. For tables already migrated and missing the owner information, perhaps the makemigrations command needs to confirm that the owner information is set correctly.
3. Not a fan of this solution myself, but one could also change the sequence is reset the ​postgres backend (i.e., not use pg_get_serial_sequence)

Long-term, maybe makemigrations should be using postgres's SERIAL and BIGSERIAL field types, which automatically create the sequence and set the sequence owner.

[Dolan Antenucci]

I have a fix, but may need help with writing my test case.. will reach out to mailing list if I can't figure out

[Tim Graham]

​PR

Please uncheck "Patch needs improvement" if you're able to rewrite the test as suggested on the PR.

[Dolan Antenucci]

FYI: I'm hoping to get to the pending changes in the coming week. Will update once they're done

[Dolan Antenucci]

Updated PR as requested; ready for review

[Mariusz Felisiak <felisiak.mariusz@…>]

In f944cb3d:

Fixed #30266 -- Kept a sequence owner when altering an AutoField/BigAutoField on PostgreSQL.