Serial Jumping

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

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)