Thread: Serials jumping

Serials jumping

From

"Matt A."

Date:

26 August 2005, 09:01:57

I have a serial column on a test box DB. I'm
using "select nextval('some_seq') as id" to insert a
id in sequence. It doesn't return the next value but
at least 4 or so (random) ahead of the current
value. Is this to be expected?

Is this the most efficient way to retreive an id value
for insert a row then insert the resulting row into
the related tables?

Is there a more efficient/proper way? I would like all
my records to increment by 1 if possible. I'd be very
grateful to read your opinion.

Thank you.



____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs

Re: Serials jumping

From

Alban Hertroys

Date:

26 August 2005, 09:19:20

Matt A. wrote:
> I have a serial column on a test box DB. I'm
> using "select nextval('some_seq') as id" to insert a
> id in sequence. It doesn't return the next value but
> at least 4 or so (random) ahead of the current
> value. Is this to be expected?

You probably did some extra selects on the sequence somewhere, or the
sequence is set to increment by a number >1.

> Is this the most efficient way to retreive an id value
> for insert a row then insert the resulting row into
> the related tables?

Why don't you just use the default value of the serial type? You can do
that by leaving the column out of the insert query.

> Is there a more efficient/proper way? I would like all
> my records to increment by 1 if possible. I'd be very
> grateful to read your opinion.

Sequences increment at every call of nextval. Doesn't matter whether you
used that value for anything or not, it always increments. Even if you
rollback the transaction or interupt the query.

Sequences ensure that multiple concurrent inserts never get the same
number back from them, so that you don't get "unique constraint
violation"'s.
They're not designed to always increment by 1.

Regards,

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

//Showing your Vision to the World//

Re: Serials jumping

From

Richard Huxton

Date:

26 August 2005, 09:25:21

Matt A. wrote:
> I have a serial column on a test box DB. I'm
> using "select nextval('some_seq') as id" to insert a
> id in sequence. It doesn't return the next value but
> at least 4 or so (random) ahead of the current
> value. Is this to be expected?

Quite possibly. The sequence generator doesn't skip around randomly, but
various connections can use/not-use a fetched value.

> Is this the most efficient way to retreive an id value
> for insert a row then insert the resulting row into
> the related tables?

Probably, yes. You can also do something like:
BEGIN;
INSERT INTO master_table (auto_id, a,b,c) VALUES (DEFAULT, 1,2,3);
INSERT INTO detail_table (another_auto_id, master_ref, x)
   VALUES (DEFAULT, currval('master_table_auto_id_seq'), 4);
...
COMMIT;

> Is there a more efficient/proper way? I would like all
> my records to increment by 1 if possible. I'd be very
> grateful to read your opinion.

You can either have:
1. A quick, easy way to get a unique number (sequences/serial types)
2. A set of IDs incrementing by 1 each time.

Make your choice. Option 2 involves an explicit counter and locking.

--
   Richard Huxton
   Archonet Ltd

Re: Serials jumping

From

"Matt A."

Date:

26 August 2005, 16:58:19

I did not know that about every call to nextval, but
that does make sense.

I need a way to return the new ID of the SERIAL column
on INSERT to add the related rows into the proper
tables explictly. Similiar to how I could use
@@identity to retrive the value in sql2000.

The only ways I saw was nextval or currval? Is there a
best practice for returning the new identities value?
I'd prefer it in one call if possible.

Thank you.


--- Alban Hertroys <alban@magproductions.nl> wrote:

> Matt A. wrote:
> > I have a serial column on a test box DB. I'm
> > using "select nextval('some_seq') as id" to insert
> a
> > id in sequence. It doesn't return the next value
> but
> > at least 4 or so (random) ahead of the current
> > value. Is this to be expected?
>
> You probably did some extra selects on the sequence
> somewhere, or the
> sequence is set to increment by a number >1.
>
> > Is this the most efficient way to retreive an id
> value
> > for insert a row then insert the resulting row
> into
> > the related tables?
>
> Why don't you just use the default value of the
> serial type? You can do
> that by leaving the column out of the insert query.
>
> > Is there a more efficient/proper way? I would like
> all
> > my records to increment by 1 if possible. I'd be
> very
> > grateful to read your opinion.
>
> Sequences increment at every call of nextval.
> Doesn't matter whether you
> used that value for anything or not, it always
> increments. Even if you
> rollback the transaction or interupt the query.
>
> Sequences ensure that multiple concurrent inserts
> never get the same
> number back from them, so that you don't get "unique
> constraint
> violation"'s.
> They're not designed to always increment by 1.
>
> Regards,
>
> --
> Alban Hertroys
> alban@magproductions.nl
>
> magproductions b.v.
>
> T: ++31(0)534346874
> F: ++31(0)534346876
> M:
> I: www.magproductions.nl
> A: Postbus 416
>     7500 AK Enschede
>
> //Showing your Vision to the World//
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>




____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs

Re: Serials jumping

From

"Joshua D. Drake"

Date:

26 August 2005, 17:14:18

Matt A. wrote:
> I did not know that about every call to nextval, but
> that does make sense.
>
> I need a way to return the new ID of the SERIAL column
> on INSERT to add the related rows into the proper
> tables explictly. Similiar to how I could use
> @@identity to retrive the value in sql2000.

Once you insert into the table that has the serial you
can use currval('sequencename') to return the value of the
id just inserted.

The caveat is that you must do it in order e.g.,

insert
currval
insert
currval

But this will work as long as you are within the same transaction.

Sincerely,

Joshua D. Drake

>
> The only ways I saw was nextval or currval? Is there a
> best practice for returning the new identities value?
> I'd prefer it in one call if possible.



>
> Thank you.
>
>
> --- Alban Hertroys <alban@magproductions.nl> wrote:
>
>
>>Matt A. wrote:
>>
>>>I have a serial column on a test box DB. I'm
>>>using "select nextval('some_seq') as id" to insert
>>
>>a
>>
>>>id in sequence. It doesn't return the next value
>>
>>but
>>
>>>at least 4 or so (random) ahead of the current
>>>value. Is this to be expected?
>>
>>You probably did some extra selects on the sequence
>>somewhere, or the
>>sequence is set to increment by a number >1.
>>
>>
>>>Is this the most efficient way to retreive an id
>>
>>value
>>
>>>for insert a row then insert the resulting row
>>
>>into
>>
>>>the related tables?
>>
>>Why don't you just use the default value of the
>>serial type? You can do
>>that by leaving the column out of the insert query.
>>
>>
>>>Is there a more efficient/proper way? I would like
>>
>>all
>>
>>>my records to increment by 1 if possible. I'd be
>>
>>very
>>
>>>grateful to read your opinion.
>>
>>Sequences increment at every call of nextval.
>>Doesn't matter whether you
>>used that value for anything or not, it always
>>increments. Even if you
>>rollback the transaction or interupt the query.
>>
>>Sequences ensure that multiple concurrent inserts
>>never get the same
>>number back from them, so that you don't get "unique
>>constraint
>>violation"'s.
>>They're not designed to always increment by 1.
>>
>>Regards,
>>
>>--
>>Alban Hertroys
>>alban@magproductions.nl
>>
>>magproductions b.v.
>>
>>T: ++31(0)534346874
>>F: ++31(0)534346876
>>M:
>>I: www.magproductions.nl
>>A: Postbus 416
>>    7500 AK Enschede
>>
>>//Showing your Vision to the World//
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 3: Have you checked our extensive FAQ?
>>
>>               http://www.postgresql.org/docs/faq
>>
>
>
>
>
>
> ____________________________________________________
> Start your day with Yahoo! - make it your home page
> http://www.yahoo.com/r/hs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/