Re: Help with pre-loaded arbitrary key sequences

On Wed, January 16, 2008 18:40, Scott Marlowe wrote:
> You're essentially wanting to fill in the blanks here. If you need
> good performance, then what you'll need to do is to preallocate all
> the numbers that haven't been assigned somewhere. So, we make a table
> something like:
>
> create table locatorcodes (i int, count_id serial);
>
> Then we insert an id into that table for everyone that's missing from
> the main table:
>
> insert into locatorcodes (i)
> select b.i from (
> select * from generate_series(1,1000000)as i
> ) as b
> left join main_table a on (b.i=a.i)
> where a.i is null;
>
> Or something like that.
>
> Now, we've got a table with all the unused ids, and a serial count
> assigned to them. Create another sequence:
>
> create checkout_sequence;
>
> and use that to "check out" numbers from locatorcodes:
>
> select i from locatorcodes where count_id=nextval('checkout_sequence');
>
> And since the sequence will just count up, there's little or no
> problems with performance.
>
> There's lots of ways of handling this. That's just one of the ones
> that doesn't slow your database down a lot.
>
> If you need to, you can shuffle the numbers going into the
> locatorcodes table with an order by random() when you create it.
>

Martin and Scott,

Thank you both for your responses.

If the entries involved numbered in the millions then Scott's approach has
considerable merit. In my case, as the rate of additions is very low and
the size of the existing blocks is in the hundreds rather than hundreds of
thousands then I believe that I will simply write my own iterator and do a
repetitive select when on the incrementally proposed values until an
opening is found then insert the new entry and update the iterator next
value accordingly.

Regards,

--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB(at)Harte-Lyne(dot)ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3