Re: serial type; race conditions

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

If you are in a transaction, and the INSERT succeeds but the transaction
rolls back, the sequence does not get reused.  Each backend has a local
variable that holds the most recent sequence assigned.  That is how
currval works.

> 
> 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.
> 
> Thanks, .... it is getting clearer....

You really can't use sequences with no gaps.  Sequence numbers are not
_held_ until commit because it would block other backends trying to get
sequence numbers.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026