Re: Equivalent for AUTOINCREMENT?

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?

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)

Michelle Konzack 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...

Yes, that's by design. If that didn't happen, then it would be
impossible for another transaction to obtain a value from the sequence
before the previous transaction had committed or rolled back, even if it
had to do lots of other unrelated work before it committed.

If you really, truly need gapless sequences, there are some options. I
posted about them recently on another thread. The archives will contain
that post and many others from many people on the same topic. Be aware,
though, that gapless sequences have some NASTY performance consequences.

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?

Design your application not to expect your primary keys to be gapless.
If it requires contiguous sequences for something, generate them at
query time instead of storing them as primary keys. If the contiguous
sequence numbers must also be stable over the life of the record, try to
redesign to avoid that requirement if at all possible.

Since I can use a transaction block if required, there should be no
problem with concurence access.

If you do not mind your transactions being unable to concurrently access
the data, there are many options available for gapless sequences. If you
have to handle DELETEs then you'll need a trigger to renumber
everything, but assuming you don't, something like this should work:

CREATE TABLE id_counter ( last_used INTEGER NOT NULL );
INSERT INTO id_counter ( last_used ) VALUES ( -1 );

-- Now, within a transaction:
--
-- Update the id counter. This obtains a lock that'll cause other
-- transactions to block until this transaction commits or rolls back
-- if they also attempt to update the counter. Only one transaction
-- (that touches id_counter) at a time may be doing work from this
-- point on.
--
-- You could use an explicit LOCK TABLE, but it's kind of pointless.
--
UPDATE id_counter SET last_used = last_used + 1;

--
-- Use our newly obtained ID, which is guaranteed to be free and
-- unused by any concurrent transaction.
--
INSERT INTO sometable ( id, blah ) VALUES ( (SELECT last_used FROM
id_counter), 'blah');

-- You can now do your other work and commit. Do as little as
-- possible after this, though, because you're preventing any
-- other transactions that need to access id_counter from doing
-- anything.

--
Craig Ringer

Michelle Konzack wrote:
-- Start of PGP signed section.

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?

You should read our three FAQ entries about sequences:

http://www.postgresql.org/docs/faqs.FAQ.html#item4.11.1

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Halle Craig,

Am 2008-11-05 20:37:31, schrieb Craig Ringer:

If you really, truly need gapless sequences, there are some options. I
posted about them recently on another thread. The archives will contain
that post and many others from many people on the same topic. Be aware,
though, that gapless sequences have some NASTY performance consequences.

Since this "NASTY performance consequences" would only hit the INSERT
statement and it is very unlikely that I have concurence WRITE/INSERT
access, it is a minor problem.

Design your application not to expect your primary keys to be gapless.
If it requires contiguous sequences for something, generate them at
query time instead of storing them as primary keys. If the contiguous
sequence numbers must also be stable over the life of the record, try to
redesign to avoid that requirement if at all possible.

Yes it is a requirement... and this is, why I have tried to get the
highest value of the column "serno".

CREATE TABLE id_counter ( last_used INTEGER NOT NULL );
INSERT INTO id_counter ( last_used ) VALUES ( -1 );
--
UPDATE id_counter SET last_used = last_used + 1;

--
INSERT INTO sometable ( id, blah ) VALUES ( (SELECT last_used FROM
id_counter), 'blah');

Thank you for the example....
I will try it out now.

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)

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

Michelle Konzack wrote:

Halle Craig,

Am 2008-11-05 20:37:31, schrieb Craig Ringer:

If you really, truly need gapless sequences, there are some options. I
posted about them recently on another thread. The archives will contain
that post and many others from many people on the same topic. Be aware,
though, that gapless sequences have some NASTY performance consequences.

Since this "NASTY performance consequences" would only hit the INSERT
statement and it is very unlikely that I have concurence WRITE/INSERT
access, it is a minor problem.

And DELETE.

And anything that happens in the same transaction after the INSERT or
DELETE that touches the table with the gapless sequence.

It'll probably be OK if you keep the transactions that modify the table
with the gapless sequences as short as possible, preferably doing
nothing except the modification in question.

--
Craig Ringer