Thread: Sequences without blank holes

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

So, a real SELECT statement would return:

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

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

Sequences?? Triggers?? Functions??

IF I had ``very few lines'' on the table, ``very few
users'' AND it was a kidding software, I would use:

$ SELECT max(prod_id)+1 FROM products;

to know the values of the next prod_id, but I really
think it is not the best way to do that.

Could you help me in this way??

Thanks in advances and
Best Regards,

Marcelo Pereira
PHP/SQL/PostgreSQL
Universidade Estadual de Campinas
São Paulo / Brazil

Yahoo! Mail - 6MB, anti-spam e antivírus gratuito. Crie sua conta agora:
http://mail.yahoo.com.br

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 Thu, 2003-11-06 at 09: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).
>
> So, a real SELECT statement would return:
>
> $ 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.
>
> How can I assure a ''sequence WITHOUT holes''?
>
> Sequences?? Triggers?? Functions??
>
> IF I had ``very few lines'' on the table, ``very few
> users'' AND it was a kidding software, I would use:
>
> $ SELECT max(prod_id)+1 FROM products;
>
> to know the values of the next prod_id, but I really
> think it is not the best way to do that.
>
> Could you help me in this way??
>
> Thanks in advances and
> Best Regards,
>
> Marcelo Pereira
> PHP/SQL/PostgreSQL
> Universidade Estadual de Campinas
> São Paulo / Brazil
>
> Yahoo! Mail - 6MB, anti-spam e antivírus gratuito. Crie sua conta agora:
> http://mail.yahoo.com.br
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

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.

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).
>
> So, a real SELECT statement would return:
>
> $ 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.
>
> How can I assure a ''sequence WITHOUT holes''?

Whatever you do, continue to use a serial field.

Now, it sounds like the users are just complaining about asthetics or
something, because both you and I know that it will work fine with the
way it is currently set up. But, you want to keep the users happy...

So, set up the table like this:

  rec_id | prod_id | description
--------+---------------------
      1  |      1  | S470DXBLM
     12  |      2  | S470DXABM
     33  |      3  | RG250DX
--------+---------------------

rec_id would be your primary key. prod_id would be what you show to the
users. Now, say someone deletes the middle record - you end up with:

  rec_id | prod_id | description
--------+---------------------
      1  |      1  | S470DXBLM
     33  |      3  | RG250DX
--------+---------------------

So, you still have a gap to be filled when the next record is added.
What you will want to do is keep the "prod_id" of the record that was
deleted - store it in a "holding" table of deleted "prod_id" records:

  rec_id | prod_id
--------+---------
      1  |      2
--------+---------

Then, when you add a record to your main table, select for the lowest
prod_id from the deleted items table, and use that prod_id to insert
into your main table, and delete the record from the deleted items
table. So, your main table would then look something like this:

  rec_id | prod_id | description
--------+---------------------
      1  |      1  | S470DXBLM
     33  |      3  | RG250DX
     34  |      2  | XYZ123
--------+---------------------

If the deleted items table is empty when you go to insert, select the
largest prod_id from the main table, and increment it by one before
adding the record.

Now, you only show "prod_id" and "description" to the users, and voila,
no more gaps (at least as far as they are concerned - rec_id will still,
of course, have gaps, as it should).

Hope this helps...

Andrew Ayers
Phoenix, Arizona

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.