Sequences without blank holes

MaRcElO PeReIrA writes:

How can I assure a ''sequence WITHOUT holes''?

$ SELECT max(prod_id)+1 FROM products;

You can do that, but

SELECT prod_id FROM products ORDER BY prod_id DESC LIMIT 1;

will be faster. In fact, if you have a B-tree index on prod_id (which you
should), it will be nearly constant time.

Also, make sure if you do a SELECT, then some client application logic,
then an UPDATE, to do it in one transaction and use the appropriate
isolation level, locking, etc.

--
Peter Eisentraut peter_e@gmx.net

The best thing is: never let the end users see the primary key. Period.
Primary keys are NOT business objects !
If your users need some IDs for the product, better assign some string
ids, but I bet the app can be written so they never need any IDs.

Just my opinion.

Cheers,
Csaba.

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

On Thu, Nov 06, 2003 at 05:01:54 -0300,
MaRcElO PeReIrA <gandalf_mp@yahoo.com.br> wrote:

$ select * from products;
prod_id | description
--------+---------------------
1 | S470DXBLM
12 | S470DXABM
33 | RG250DX
--------+---------------------
(3 rows)

and it is ok to me, but not to the users.

I aggree with the suggestion not to show them the internal keys.
The values in the description field look a lot more like product IDs
to me than descriptions. Maybe you should just use those when interacting
with the users.