Re: serial type; race conditions

Thus spake postgresql
> How does currval work if you are not inside a transaction. I have
> been experimenting with inserting into a table that has a sequence.
> If the insert fails (not using a transaction) because of bad client input
> then the next insert gets the proper next number in the sequence.
>
> given sequence 1,2,3,4,5 exists
> insert into table date 1/111/01 (obviously wrong) insert fails...
> try again with good data, insert succeeds and gets number 6 in the
> sequence.
>
> i'm getting what I want. A sequence number that does not increment
> on a failed insert. However, how do I get the assigned sequence
> number with currval when I am not using a transaction? What
> happens when multiple users are inserting at the same time?
>
> I am trying to create a sequence with out any "missing" numbers. If
> there is a failure to insert, and a sequence number is "taken". I want
> the empty row.

Why is it a requirement to not use transactions? That's the proper way
to maintain data consistency in a relational database.

I have suggested a method before to create a sequence with out any missing
numbers but let me explain it further. I have never used this method with
PostgreSQL but I am sure it has the features needed to make it work.

Create a table called numbers with two fields, seq_name and seq_num. Pick a
maximum number of concurrent users for a specific serial number. Seed the
table with the sequence name and number for each sequence name and number
from 1 to the maximum you picked. So, if you had 3 sequences and 10 as
the max the table should have 30 entries in it.

To get the next number you start a transaction. You then search for the
lowest number in the table for the required sequence name that is not locked
by another process (this is the part I am fuzzy on for PostgreSQL - how do
you exclude locked records from your search just based on them being locked?)
and lock it. You then use that number in your transaction and add your max
to it. In the above example of 10 then the first time you use it you will get
1 and the number will be increased to 11. If your transaction is rolled back
then you may have a hole if someone else used a number but it will be filled
the next time that someone requests a number from that sequence. They aren't
strictly speaking consecutive but they are complete.

--
D'Arcy J.M. Cain <darcy(at){druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.