Re: problem with serial

On Fri, Apr 20, 2012 at 1:05 AM, Yvon Thoraval <yvon(dot)thoraval(at)gmail(dot)com> wrote:
> 2012/4/19 Chris Angelico <rosuav(at)gmail(dot)com>
>> As a side point, I would recommend against doing this. Once you've
>> "used" a rowid, it's not worth reusing it. You'll save yourself some
>> headaches down the track if you simply ignore those odd gaps (ditto
>> the gaps that result from rolled-back transactions) and just DELETE
>> the rows you're throwing out without bothering to setval the sequence
>> back.
>
> Yes, for sure, however, i did this kind of workaround, because one time I've
> seen a rowid of 8, after an insertion even if the max(rowid) was of 42.

If all your inserts make use of the sequence, and you never alter the
sequence, then this should never happen (unless, that is, 34 other
inserts happened between when you inserted and when you checked the
max). Be extremely careful of selecting max(rowid) when you have
concurrent transactions; it's entirely possible that some other
transaction has consumed a value from the sequence but hasn't yet
written it to the database (at least, not in any way that your
transaction can see), which means you risk resetting the sequence too
low.

ChrisA