Sequence in PostgreSQL is not updated when doing manual insert

[pgross]

In order to load some test data into the database, I'm using the
following code:

from django.core.db import db
cursor = db.cursor()

cursor.execute("insert into cities (id, name) values (1,
'Chicago');")
cursor.execute("insert into cities (id, name) values (2,
'Miami');")
...

This works fine, but then when I try to use django later to add to this
table, I get an error:

cities.City(name='Madison').save()

ERROR: duplicate key violates unique constraint "cities_pkey"

I've tracked down the problem, and it seems that when I do my manual
inserts, the sequence (cities_id_seq) that PostgreSQL uses for primary
keys isn't updated. Therefore, when I try to save my object with
django, the primary key collides with an already existing key.

I've implemented a simple fix by manually settings the sequence value
after I do my inserts, but I'm wondering why django/postgresql doesn't
do the right thing. Has anyone seen this before?

[Jacob Kaplan-Moss]

On Nov 23, 2005, at 11:15 AM, pgross wrote:
> I've tracked down the problem, and it seems that when I do my manual
> inserts, the sequence (cities_id_seq) that PostgreSQL uses for primary
> keys isn't updated. Therefore, when I try to save my object with
> django, the primary key collides with an already existing key.
>
> I've implemented a simple fix by manually settings the sequence value
> after I do my inserts, but I'm wondering why django/postgresql doesn't
> do the right thing. Has anyone seen this before?

Yeah, this is an annoying postgresql wart. We've run into it enough
that we've rolled the fix into the django-admin utility. "django-
admin sqlsequencereset <app>" will print out the statements to fix
the sequences.

Programmatically, you can get at this SQL from the
django.core.management.get_sql_sequence_reset() function.

Jacob

[pgross]

Thanks, I didn't notice that function before. Maybe the docs could
specify when and why you'd want to use it, such as my case above.

Paul

[Joshua D. Drake]

>>
>> I've implemented a simple fix by manually settings the sequence value
>> after I do my inserts, but I'm wondering why django/postgresql doesn't
>> do the right thing. Has anyone seen this before?
>
>
> Yeah, this is an annoying postgresql wart. We've run into it enough
> that we've rolled the fix into the django-admin utility. "django-

> admin sqlsequencereset <DEFANGED_app>" will print out the statements
> to fix the sequences.

Well I wouldn't say this has anything to do with a postgresql wart as
much as somebody not understanding how sequences work.
A simple setval() would have solved the problem after load.

Joshua D. Drake

>
> Programmatically, you can get at this SQL from the
> django.core.management.get_sql_sequence_reset() function.
>
> Jacob

--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PLphp, PLperl - http://www.commandprompt.com/