#30266: Migrating a model's default primary key to a BigAutoField causes
Postgres
sequence to lose owner
-------------------------------------+-------------------------------------
Reporter: Dolan Antenucci | Owner: Dolan
| Antenucci
Type: Bug | Status: assigned
Component: Migrations | Version: 1.11
Severity: Normal | Resolution:
Keywords: postgres migration | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Tim Graham):
* easy: 1 => 0
Old description:
> Note: I'm marking this as easy pickings, but feel free to change.
>
> == Summary of Issue ==
> Start with the following models (below assumes application name is
> "sandbox"):
>
> {{{#!python
> 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`:
>
> {{{#!python
> 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
> [https://github.com/django/django/blob/master/django/db/backends/postgresql/operations.py#L157
> 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
> [https://www.postgresql.org/docs/10/datatype-numeric.html#DATATYPE-SERIAL
> 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
> [https://github.com/django/django/blob/master/django/db/backends/postgresql/operations.py#L157
> 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.
New description:
== Summary of Issue ==
Start with the following models (below assumes application name is
"sandbox"):
{{{#!python
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`:
{{{#!python
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
[https://github.com/django/django/blob/master/django/db/backends/postgresql/operations.py#L157
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
[https://www.postgresql.org/docs/10/datatype-numeric.html#DATATYPE-SERIAL
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
[https://github.com/django/django/blob/master/django/db/backends/postgresql/operations.py#L157
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/30266#comment:3>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.26b70cc3b12130b689455bbfdf5ee4d7%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
