We have a table here with a serial value in it.
We have sets of test data that we run through a processor that changes
a fairly large set of tables in deterministic ways.
Sometimes (about 20%, it seems) with several of the data sets, we
get an error trying to insert rows into the table with the serial in it.
On investigation, it seems that the serial number has got to 101, then
set itself back to 4, causing nextval to return 5, and there are already
entries from 1-101.
Now, we use the serial as the primary key, and we never explicitly set it.
Has anyone seen anything like this? I can work around it by generating
a serial number within the application, but that's not ideal.
Is this another RTFM question?
Stephen