Re: Serials jumping

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//