| Lists: | pgsql-general |
|---|
| From: | db(dot)subscriptions(at)shepherdhill(dot)biz |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Serial Jumping |
| Date: | 2009-01-27 04:55:48 |
| Message-ID: | 20090127055548.bpcc4i3hcgs00sco@webmail.shepherdhill.biz |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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
| From: | Bill Moran <wmoran(at)potentialtech(dot)com> |
|---|---|
| To: | db(dot)subscriptions(at)shepherdhill(dot)biz |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Serial Jumping |
| Date: | 2009-01-27 05:14:48 |
| Message-ID: | 20090127001448.aa35061b.wmoran@potentialtech.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
db(dot)subscriptions(at)shepherdhill(dot)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
| From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
|---|---|
| To: | Bill Moran <wmoran(at)potentialtech(dot)com> |
| Cc: | db(dot)subscriptions(at)shepherdhill(dot)biz, pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Serial Jumping |
| Date: | 2009-01-27 05:24:23 |
| Message-ID: | 497E9A87.50305@postnewspapers.com.au |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Bill Moran wrote:
> db(dot)subscriptions(at)shepherdhill(dot)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
| From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Serial Jumping |
| Date: | 2009-01-27 06:10:18 |
| Message-ID: | 20090127061018.GA29255@a-kretschmer.de |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
In response to db(dot)subscriptions(at)shepherdhill(dot)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
| From: | Devrim GÜNDÜZ <devrim(at)gunduz(dot)org> |
|---|---|
| To: | db(dot)subscriptions(at)shepherdhill(dot)biz |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Serial Jumping |
| Date: | 2009-01-27 16:29:01 |
| Message-ID: | 1233073741.15799.9.camel@laptop.gunduz.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
On Tue, 2009-01-27 at 05:55 +0100, db(dot)subscriptions(at)shepherdhill(dot)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
| From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Serial Jumping |
| Date: | 2009-02-01 10:21:22 |
| Message-ID: | gm3t32$8nf$1@reversiblemaps.ath.cx |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
On 2009-01-27, db(dot)subscriptions(at)shepherdhill(dot)biz <db(dot)subscriptions(at)shepherdhill(dot)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)