Re: Sequences without blank holes

On Thursday 06 November 2003 08:01, MaRcElO PeReIrA wrote:
> Hi guys,
>
> I have been using the following table (short, short,
> short version):
>
> CREATE TABLE products (
>     prod_id SERIAL,
>     description TEXT
> );
>
> BUT, there is lots os users blaming because the holes
> in the [prod_id] field (and, of course it as supposed
> to be like this, because sequences only increase their
> values and never rollback).

Well, whatever you do you're going to serialise any additions to the products 
table, so that's going to be a bottleneck.

I personally tend to have a system_settings table with a next_id row.
CREATE TABLE system_settings_int (
  setting varchar(100),
  value int4
);

You need to :
 - lock the row in question
 - increment it and read the new value
 - insert your product with the id in question
 - commit the transaction, releasing the lock

Of course this means that no other users can insert until the first user 
inserts, and you'll need to deal with failed inserts in your application.

What you don't want to do is get the next value, let the user edit the product 
details then insert - that'll make everything grind to a halt.
-- 
  Richard Huxton
  Archonet Ltd