Re: Equivalent for AUTOINCREMENT?

On Sat, Nov 1, 2008 at 12:33 PM, Michelle Konzack
<linux4michelle@tamay-dogan.net> wrote:
> Hello Michael,
>
> Am 2008-10-31 11:15:54, schrieb Michael Hall:
>> > I have a table where I have a serialnumber which shuld be  increased  be
>> > each INSERT.  I know I can use max() to get the highest number, but  how
>> > can I use it in a INSERT statement?
>>
>> Have a look in the manual for the SERIAL data type.
>> For fields with a SERIAL data type, you can use DEFAULT in an INSERT statement.
>
> OK, the SERIAL is now working, but there is ONE problem.
>
> If the INSERT fails, the SERIAL is in any case increased...
>
> So this was the reason, why I have tried to use max(serno)  and  add  +1
> the get the next value for "serno".
>
> Is there a solution for it?

Note that this is not only how pgsql and oracle and most other big
databases work.  It's also how innodb tables in mysql work:

http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html

and for good reason, that as mentioned elsewhere, gapless sequences
tend to scale poorly.


>
> Since I can use a transaction block if  required,  there  should  be  no
> problem with concurence access.
>
> Thanks, Greetings and nice Day/Evening
>    Michelle Konzack
>    Systemadministrator
>    24V Electronic Engineer
>    Tamay Dogan Network
>    Debian GNU/Linux Consultant
>
>
> --
> Linux-User #280138 with the Linux Counter, http://counter.li.org/
> ##################### Debian GNU/Linux Consultant #####################
> Michelle Konzack   Apt. 917                  ICQ #328449886
> +49/177/9351947    50, rue de Soultz         MSN LinuxMichi
> +33/6/61925193     67100 Strasbourg/France   IRC #Debian (irc.icq.com)
>



--
When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis