Re: Changing ids conflicting with serial values?

At 11:49 04/11/2005 -0500, Alex Turner wrote:
> I think he meant
>
> create sequence test_seq;
> select setval('test_seq',(select max(primary_key_id) from my_table));
>
> not max value of a serial type.

What I understand, and from what I know by using mysql, is that mysql
auto-adjust the max value of a serial.
Something like :
- a table with 5000 elements with ids from 1-5000.
- if you update the id in one of the rows and set it to 65000, mysql
updates the serial current value.
- even if you delete, or change the id back to its previous value, the
current value will still be 65000.
- a new inserted row will have the id 65001.

Now assuming the id's maximum value is 65535, and you set one of the rows to
this value, mysql will be unable to find a "nextval" greater than 65535. New
inserts will fail.
I don't know if it is still the case with recent versions of mysql, but that's
what I discovered while testing a web application.

Just to say that even mysql has its problems when a user plays with serial PK.

--
Marc