By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,904 Members | 1,089 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,904 IT Pros & Developers. It's quick & easy.

Sequences without blank holes

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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 pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #2

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #3

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #4

P: n/a
On Thu, Nov 06, 2003 at 05:01:54 -0300,
MaRcElO PeReIrA <ga********@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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.