[Django] #28272: PostgreSQL: AutoField sequences don't increment when inserting objects with an explicitely specified PK
Django
an explicitely specified PK
-------------------------------------+-------------------------------------
Reporter: François | Owner: nobody
Freitag |
Type: Bug | Status: new
Component: Database | Version: master
layer (models, ORM) |
Severity: Normal | Keywords: postgresql sequence
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Developers are allowed to specify primary keys for newly created python
instances [1]. However, on PostgreSQL, the sequence attached to the serial
is not updated to take manually set PKs into account.
The following queries fail on PostgreSQL:
{{{
CREATE TABLE foo (id serial primary key, bar text);
INSERT INTO foo VALUES (1, 'thing');
INSERT INTO foo (bar) VALUES ('other');
-- ERROR: duplicate key value violates unique constraint "foo_pkey"
-- DETAIL: Key (id)=(1) already exists.
}}}
Hence, the following test case fails in Django:
{{{#!python
# Using models from tests/bulk_create/models.py
def test_create_explicit_pk(self):
Country.objects.create(name='France', iso_two_letter='FR')
next_pk = Country.objects.latest('pk').pk + 1
Country.objects.create(pk=next_pk, name='US', iso_two_letter='US')
Country.objects.create(name='NL', iso_two_letter='NL') # FAILS
because next_pk is already in use.
# The sequence should have yielded next_pk + 1
}}}
In my opinion, the sequence attached to the primary key should be manually
set to the pk after inserting an object with an explicitly specified pk.
The same issue can be observed for {{{bulk_create}}}, where I think the
highest primary key of the batch should be used to determine the next
value in the DB sequence (i.e. use setval [2]).
This is the behavior in MariaDB [3] and SQLite [4].
Oracle might be also be affected by this issue. I was able to find this
old ticket on the bug tracker[5].
[1] https://docs.djangoproject.com/en/dev/ref/models/instances
/#explicitly-specifying-auto-primary-key-values
[2] https://www.postgresql.org/docs/current/static/functions-sequence.html
[3] https://mariadb.com/kb/en/mariadb/auto_increment/#setting-explicit-
values
[4] https://sqlite.org/autoinc.html
[5] https://code.djangoproject.com/ticket/6598
--
Ticket URL: <https://code.djangoproject.com/ticket/28272>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
an explicitely specified PK
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql sequence | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
Developers are allowed to specify primary keys for newly created python
instances [1]. However, on PostgreSQL, the sequence attached to the serial
is not updated to take manually set PKs into account.
The following queries fail on PostgreSQL:
{{{
CREATE TABLE foo (id serial primary key, bar text);
INSERT INTO foo VALUES (1, 'thing');
INSERT INTO foo (bar) VALUES ('other');
-- ERROR: duplicate key value violates unique constraint "foo_pkey"
-- DETAIL: Key (id)=(1) already exists.
}}}
Hence, the following test case fails in Django:
{{{#!python
# Using models from tests/bulk_create/models.py
def test_create_explicit_pk(self):
Country.objects.create(name='France', iso_two_letter='FR')
next_pk = Country.objects.latest('pk').pk + 1
Country.objects.create(pk=next_pk, name='US', iso_two_letter='US')
Country.objects.create(name='NL', iso_two_letter='NL') # FAILS
because next_pk is already in use.
# The sequence should have yielded next_pk + 1
}}}
In my opinion, the sequence attached to the primary key should be manually
set to the next available PK after inserting an object with an explicitly
specified PK (e.g. using setval [2]). The same issue can be observed for
{{{bulk_create}}}, where I think the highest primary key of the batch
should be used to determine the next value in the DB sequence.
MariaDB [3] and SQLite [4] update the next value in the sequence when
autoincrement is used.
Oracle might be also be affected by this issue. I was able to find this
old ticket on the bug tracker[5].
[1] https://docs.djangoproject.com/en/dev/ref/models/instances
/#explicitly-specifying-auto-primary-key-values
[2] https://www.postgresql.org/docs/current/static/functions-sequence.html
[3] https://mariadb.com/kb/en/mariadb/auto_increment/#setting-explicit-
values
[4] https://sqlite.org/autoinc.html
[5] https://code.djangoproject.com/ticket/6598
--
--
Ticket URL: <https://code.djangoproject.com/ticket/28272#comment:1>
Django
an explicitely specified PK
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> Developers are allowed to specify primary keys for newly created python
> instances [1]. However, on PostgreSQL, the sequence attached to the
> serial is not updated to take manually set PKs into account.
>
> The following queries fail on PostgreSQL:
> {{{
> CREATE TABLE foo (id serial primary key, bar text);
> INSERT INTO foo VALUES (1, 'thing');
> INSERT INTO foo (bar) VALUES ('other');
> -- ERROR: duplicate key value violates unique constraint "foo_pkey"
> -- DETAIL: Key (id)=(1) already exists.
> }}}
>
> Hence, the following test case fails in Django:
>
> {{{#!python
> # Using models from tests/bulk_create/models.py
> def test_create_explicit_pk(self):
> Country.objects.create(name='France', iso_two_letter='FR')
> next_pk = Country.objects.latest('pk').pk + 1
> Country.objects.create(pk=next_pk, name='US', iso_two_letter='US')
> Country.objects.create(name='NL', iso_two_letter='NL') # FAILS
> because next_pk is already in use.
> # The sequence should have yielded next_pk + 1
> }}}
>
> In my opinion, the sequence attached to the primary key should be
> manually set to the next available PK after inserting an object with an
> explicitly specified PK (e.g. using setval [2]). The same issue can be
> observed for {{{bulk_create}}}, where I think the highest primary key of
> the batch should be used to determine the next value in the DB sequence.
>
> MariaDB [3] and SQLite [4] update the next value in the sequence when
> autoincrement is used.
>
> Oracle might be also be affected by this issue. I was able to find this
> old ticket on the bug tracker[5].
>
> [1] https://docs.djangoproject.com/en/dev/ref/models/instances
> /#explicitly-specifying-auto-primary-key-values
> [2] https://www.postgresql.org/docs/current/static/functions-
> sequence.html
> [3] https://mariadb.com/kb/en/mariadb/auto_increment/#setting-explicit-
> values
> [4] https://sqlite.org/autoinc.html
> [5] https://code.djangoproject.com/ticket/6598
New description:
Developers are allowed to specify primary keys for newly created python
instances [1]. However, on PostgreSQL, the sequence attached to the serial
is not updated to take manually set PKs into account.
The following queries fail on PostgreSQL:
{{{
CREATE TABLE foo (id serial primary key, bar text);
INSERT INTO foo VALUES (1, 'thing');
INSERT INTO foo (bar) VALUES ('other');
-- ERROR: duplicate key value violates unique constraint "foo_pkey"
-- DETAIL: Key (id)=(1) already exists.
}}}
Hence, the following test case fails in Django:
{{{#!python
# Using models from tests/bulk_create/models.py
def test_create_explicit_pk(self):
Country.objects.create(name='France', iso_two_letter='FR')
next_pk = Country.objects.latest('pk').pk + 1
Country.objects.create(pk=next_pk, name='US', iso_two_letter='US')
Country.objects.create(name='NL', iso_two_letter='NL') # FAILS
because next_pk is already in use.
# The sequence should have yielded next_pk + 1
}}}
In my opinion, the sequence attached to the primary key should be manually
set to the next available PK after inserting an object with an explicitly
specified PK (e.g. using setval [2]). The same issue can be observed for
{{{bulk_create}}}, where I think the highest primary key of the batch
should be used to determine the next value in the DB sequence.
MariaDB [3] and SQLite [4] update the next value correctly when
autoincrement is used.
Oracle might be also be affected by this issue. I was able to find this
old ticket on the bug tracker[5].
[1] https://docs.djangoproject.com/en/dev/ref/models/instances
/#explicitly-specifying-auto-primary-key-values
[2] https://www.postgresql.org/docs/current/static/functions-sequence.html
[3] https://mariadb.com/kb/en/mariadb/auto_increment/#setting-explicit-
values
[4] https://sqlite.org/autoinc.html
[5] https://code.djangoproject.com/ticket/6598
--
--
Ticket URL: <https://code.djangoproject.com/ticket/28272#comment:2>
Django
an explicitely specified PK
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: felixxm (added)
Comment:
Oracle backend is not affected on master, because we use identity columns
https://github.com/django/django/commit/924a89e135fe54bc7622aa6f03405211e75c06e9.
I confirm that there is an issue in the 1.11 on Oracle.
--
Ticket URL: <https://code.djangoproject.com/ticket/28272#comment:3>
Django
an explicitely specified PK
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
I'm doubtful that trying to make Django automatically set the sequence is
a good idea. In particular, I'm thinking about thread safety of such a
solution. Quoting James Bennett from comments 1 & 3 of #11423,
When you do [manually specify a primary key] on Postgres, you need to
reset the sequence from which automatic values are generated (since that
sequence doesn't know that you manually inserted some values, and so those
values are no longer available for use); this is a known feature of
Postgres, and Django provides a management command
([https://docs.djangoproject.com/en/dev/ref/django-admin/#sqlsequencereset
sqlsequencereset]) which will output the appropriate SQL for you to run to
reset the sequence. \\ \\
Friendly or not, this is the way Postgres, Oracle, DB2 and many other
databases work. MySQL and SQLite are really more the exceptions here,
since sequences are part of the SQL standard.
--
Ticket URL: <https://code.djangoproject.com/ticket/28272#comment:4>
Django
an explicitely specified PK
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by François Freitag):
I do not think that manually specifying PK is a good idea, and I agree
that resetting the sequence is subject to race conditions and might not be
a good idea.
I think we should at least update the documentation to make it clear that
specifying pk in {{{create}}} or {{{bulk_create}}} can lead to future
issues on PostgreSQL. The documentation already states:
Explicitly specifying auto-primary-key values is mostly useful for
bulk-saving objects, when you’re confident you won’t have primary-key
collision.
I think manually setting primary keys on PostgreSQL should discouraged, or
a link to sqlresetsequence should be added (even though using this
management command seem like a very manual process).
--
Ticket URL: <https://code.djangoproject.com/ticket/28272#comment:5>
Django
an explicitely specified PK
-------------------------------------+-------------------------------------
Component: Database layer | Version: master
(models, ORM) |
Keywords: postgresql sequence | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => wontfix
Comment:
Documenting the current behavior is a duplicate of #18485.
--
Ticket URL: <https://code.djangoproject.com/ticket/28272#comment:6>