How to avoid out of sync sequence for primary key using Postgresql?

[cfobel]

Hi,

My setup is:

Django 1.0 (rev 9066)
Postgresql v8.3

I recently encountered a situation where the sequence for the primary
key of one of my models became out of sync, which caused the following
error when trying to save a new model instance:

IntegrityError ... 'duplicate key violates unique constraint
"myapp_mymodel_pkey"'

This is because the sequence was returning IDs that were already in
use, so there was a conflict when trying to insert a new record with
the same ID. I found the solution was to reset the sequence using the
output from './manage.py sqlsequencereset <app>'. This solution
eliminated the error, but I'm left a little perplexed. I was hoping
that someone might have some explanation for why the sequence would be
out of sync in the first place.

My understanding is that this should only happen if manual insertion
is done to the database where an ID is specified (perhaps as the
result of a database restore from a dump). I haven't done anything
like that, so I'm not sure what would have caused it in my case. I
_have_ done database table schema changes - i.e. adding new columns,
etc. Could this cause the sequence to reset?

Has anyone else has similar issues? Has anyone come up with a more
elegant solution than having to reset the sequence after the fact?
Does anyone have any suggestions on how to prevent the sequence from
going out of sync in the first place?

Any advice would be greatly appreciated!

Thanks,
Christian

[cfobel]

Oh, and I want to emphasize that I am open to any constructive
suggestions - if this is a known issue with Postgresql that doesn't
have a reasonable solution, I'd be open to switching to MySQL, etc. if
necessary. (Obviously my preference would be to stick with Postgresql
because that's what our team is already using, but I'm willing to
consider switching)

Thanks,
Christian

[David Durham, Jr.]

On Thu, Oct 16, 2008 at 2:38 PM, cfobel <chri...@brainpark.com> wrote:
> I recently encountered a situation where the sequence for the primary
> key of one of my models became out of sync, which caused the following
> error when trying to save a new model instance:
>
> IntegrityError ... 'duplicate key violates unique constraint
> "myapp_mymodel_pkey"'

Like you said, if you inserted a record with a manual ID greater than
the current sequence value, assuming you're moving forward through a
sequence of numbers from lower numbers to greater ones, then you could
see this. Or if you actually changed the sequence. You might have
ran an 'alter sequence' statement.

Switching to MySQL is not going to help you out here.

-Dave

[cfobel]

The reason I ask about whether this problem is eliminated by using
MySQL is that according to the Django docs:
<quote>
sqlsequencereset [appname appname …]

Prints the SQL statements for resetting sequences for the given app
names.

You’ll need this SQL only if you’re using PostgreSQL and have inserted
data by hand. When you do that, PostgreSQL’s primary key sequences can
get out of sync from what’s in the database, and the SQL emitted by
this command will clear it up.
</quote>

Does anyone definitively know whether MySQL automatically increments
its sequences (or whatever their equivalent name is) even if records
are manually inserted with a higher ID?

[Karen Tracey]

On Wed, Oct 22, 2008 at 2:16 PM, cfobel <chri...@brainpark.com> wrote:
> Does anyone definitively know whether MySQL automatically increments
> its sequences (or whatever their equivalent name is) even if records
> are manually inserted with a higher ID?

Seems to:

mysql> describe color;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| value | varchar(10) | NO   |     |         |                |
| warm  | tinyint(1)  | NO   |     |         |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> insert into color (value, warm) values ('Red', 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into color (value, warm) values ('Blue', 0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from color;
+----+-------+------+
| id | value | warm |
+----+-------+------+
|  1 | Red   |    1 |
|  2 | Blue  |    0 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql> insert into color (id, value, warm) values (8, 'Orange', 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into color (value, warm) values ('Green', 0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from color;
+----+--------+------+
| id | value  | warm |
+----+--------+------+
|  1 | Red    |    1 |
|  2 | Blue   |    0 |
|  8 | Orange |    1 |
|  9 | Green  |    0 |
+----+--------+------+
4 rows in set (0.00 sec)

This is with MySQL server 5.0.  Before depending on the behavior I'd probably want to carefully read the docs about auto increment:

http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

Also, in your situation I don't know I'd be considering switching  DBs to avoid the error you hit.  If I understand correctly, though you did not intentionally manually assign IDs that should have been auto-generated, somehow the sequence got out of sync with what was in the DB.  I'd really want to understand how that happened and fix the root cause.  Whatever it is that manually assigned IDs might cause worse problems down the road (overwriting existing objects with new, doing unexpected things when a manually assigned ID overlaps an already-in-use one, using an ID so high that MySQL runs out of possible values, etc.).  I think I'd rather know sooner rather than later that something screwy is going on with the auto-id fields, myself, and it sounds like PostgreSQL will likely report the problem sooner.

Karen