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

[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 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