Thread: Serial Jumping
Hi, I have a table with BIG SERIAL field as Primary KEY. During high load, entries in the BIG SERIAL field are jumped. One could see a row with 1367 and expecting the next INSERT to be 1368, one would end up getting 1369. Please is this normal? Regards, Chris
db.subscriptions@shepherdhill.biz wrote: > > Hi, > > I have a table with BIG SERIAL field as Primary KEY. During high load, > entries in the BIG SERIAL field are jumped. One could see a row with > 1367 and expecting the next INSERT to be 1368, one would end up > getting 1369. > > Please is this normal? If transactions rollback, the serial value assigned during the rolled back transaction is skipped. This has been discussed many times, it's a tradeoff between losing some #s now and again and taking a huge performance and code complexity hit to avoid it. If you absolutely need consecutive #s, then serial is not for you and you should implement your own method of acquiring sequential numbers. -- Bill Moran http://www.potentialtech.com
Bill Moran wrote: > db.subscriptions@shepherdhill.biz wrote: >> Hi, >> >> I have a table with BIG SERIAL field as Primary KEY. During high load, >> entries in the BIG SERIAL field are jumped. One could see a row with >> 1367 and expecting the next INSERT to be 1368, one would end up >> getting 1369. >> >> Please is this normal? > > If transactions rollback, the serial value assigned during the rolled > back transaction is skipped. This has been discussed many times, it's > a tradeoff between losing some #s now and again and taking a huge > performance and code complexity hit to avoid it. > > If you absolutely need consecutive #s, then serial is not for you and > you should implement your own method of acquiring sequential numbers. You should also understand the several LARGE downsides to doing so. See repeated past mailing list discussion. -- Craig Ringer
In response to db.subscriptions@shepherdhill.biz : > Hi, > > I have a table with BIG SERIAL field as Primary KEY. During high load, > entries in the BIG SERIAL field are jumped. One could see a row with > 1367 and expecting the next INSERT to be 1368, one would end up > getting 1369. > > Please is this normal? Yes. Because a serial can't rolled back. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Tue, 2009-01-27 at 05:55 +0100, db.subscriptions@shepherdhill.biz wrote: > I have a table with BIG SERIAL field as Primary KEY. During high load, > entries in the BIG SERIAL field are jumped. One could see a row with > 1367 and expecting the next INSERT to be 1368, one would end up > getting 1369. > > Please is this normal? Yes, but there is a way to get rid of that: http://www.varlena.com/GeneralBits/130.php Regards, -- Devrim GÜNDÜZ, RHCE devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org
Attachment
On 2009-01-27, db.subscriptions@shepherdhill.biz <db.subscriptions@shepherdhill.biz> wrote: > Hi, > > I have a table with BIG SERIAL field as Primary KEY. During high load, > entries in the BIG SERIAL field are jumped. One could see a row with > 1367 and expecting the next INSERT to be 1368, one would end up > getting 1369. > > Please is this normal? if an insert that would have used 1368 failed or is in an unfinished transaction that's entirely normal. if you care about the value you are inserting make sure you know it as the time it is inserted (use returning or use nextval beforehand)