#28272: PostgreSQL: AutoField sequences don't increment when inserting objects
with
an explicitely specified PK
-------------------------------------+-------------------------------------
Reporter: François Freitag | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql sequence | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by François Freitag:
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 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
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 <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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/073.b556c202cc47bcef155d03d8d882e174%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
