Opened 7 years ago

Last modified 7 years ago

#28272 closed Bug

PostgreSQL: AutoField sequences don't increment when inserting objects with an explicitely specified PK — at Version 2

Reported by: François Freitag Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: postgresql sequence
Cc: Mariusz Felisiak Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by François Freitag)

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:

# 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

Change History (2)

comment:1 by François Freitag, 7 years ago

Description: modified (diff)

comment:2 by François Freitag, 7 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top