Обсуждение: Insert unique fails, still increments ID in a lookup table

Hi,

Could you please give me some hints how to optimize this in my DB? I have a main table and a lookup table as follows:

create table main (
      id bigserial primary key;
   item_id bigint references lookup (item_id);
);

create table lookup (
  item_id bigserial primary key;
  item_name text unique not null;
);

When a new item arrives (in a temp table), I'm checking if it's already in the lookup table and insert it if not. I do
thiswith a trigger function using the following snippet: 
------
  begin
    insert into lookup values (default,NEW.item_name) returning item_id into itid;
  exception
    when unique_violation then
    select into itid item_id from lookup where item_name=NEW.item_name;
  end;
  NEW.item_id := itid;
------

The problem is that the uniqueness check apparently increases the serial counter and hence I burn through the bigint
IDsmuch faster. It's a waste for 100m+ records... 

An example result for the main table where the second item arrives at the 4th record:

id | item_id
----------------
1 | 1
2 | 1
3 | 1
4 | 4
5 | 5
...

the lookup table becomes:

item_id | item_name
----------------------------
1 | apple
4 | orange
5 | banana
...

Any thoughts?

Thanks,
Mark

PS: I'd like to keep the unique property, because it makes the insert check fast and simple.

> Any thoughts?

Yeah, the heat death of the universe will occur before you use up bigint
ids. Of course you could do a quick check for uniqueness first, then only
enter a begin/exception block for atomicity if the value was unique.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

> The problem is that the uniqueness check apparently increases the serial counter and hence I burn through the bigint
IDsmuch faster. It's a waste for 100m+ records... 

It's a little hidden but

http://www.postgresql.org/docs/current/static/functions-sequence.html

Per the last sentence, updates to the sequence are never rolled back -
the insert is always going to increment the id regardless of whether it
hits the exception or not.

--
Postgresql & php tutorials
http://www.designmagick.com/