Hi, On Tue, 2004-10-19 at 01:16, Eric E wrote: > Hi, > I have a question about sequences. I need a field to have values with > no holes in the sequence. However, the values do not need to be in order. > > My users will draw a number or numbers from the sequence and write to > the field. Sometimes, however, these sequence numbers will be discarded > (after a transaction is complete), and thus available for use. During > the transaction, however, any drawn numbers need to be unavailable. > I would like the next user who draws a number to draw the lowest number > she can, starting with the holes in the sequence. > > This continuous sequence is absolutely required by our company, as the > fact that the sequence has no holes is used to check for much more > serious problems.
I would recheck this requirement. What should actually be achieved with the check for no holes in the numbering? Remember you can always enumerate using a set returning function or by means of a temporary sequence for a query. > So my question is: > what's the most effective way to get the next available number? There is none. > My present method is to do a query that finds the first and last number > in each of the holes, step through those holes, and then start > generating new numbers. Unfortunately, this involves doing a table scan > each time - before I generate the number, and does not produce the > transaction-safety I want. You cannot eat the cake and keep it - either you have holes or you have transaction security or you have bad performance by locking the whole table on insert. Regards Tino ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match