Autoincrement separately for each foreign key

Question

I have two tables:

 CREATE TABLE "user"
    (
      username character varying(35) NOT NULL,
      CONSTRAINT user_pk PRIMARY KEY (username)
    )
    CREATE TABLE item
    (
      id serial NOT NULL,
      username character varying(35),
      user_item_number integer,
      item_value character varying(35),
      CONSTRAINT item_pk PRIMARY KEY (id),
      CONSTRAINT item_fk FOREIGN KEY (username)
          REFERENCES "user" (username) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION,
      CONSTRAINT unique_item_username UNIQUE (username, user_item_number)
    )

I would like to auto increment user_item_number separately for each username. Following image shows example. For each username: (user1, user2) user_item_number starts form 1 and is incremented by 1.

I suppose that I should use some trigger before insert which get max value of user_item_number for username which is inserted and increment it. But I don't know how to write that trigger.

I also don't know how to consider a concurency access (concurency insert more than one row with the same value of username). I don't want to get constraint violation error when two rows with the same username and user_item_number are inserted, I would like that trigger catch that error and one again increment user_item_number value.

Any idea?

Voyage

I don't actually know whether this is possible, but can you use a select statement for a default value? (I.e. "select count (id) from item where username=x" would get the value you want, right? Not sure about concurrency, though.) – Chon

You are right, I can use query: INSERT INTO item( username, user_item_number, item_value) VALUES ('user2',(select count(user_item_number)+1 as max from item where username='user2'), 'new value'); but I prefer some method in db which make it for me. This solution does not solve concurency problem, sometimes I get contraint violations and I will have to execute query again. – Voyage

Answer 1

I found solution. I wrote trigger and procedure:

create OR REPLACE function myinsert() RETURNS trigger as $$
    BEGIN
        if NEW.user_item_number is not null then return NEW;
        end if;
        loop
            <<roolbac_to>>
            declare
                max INTEGER:=null;
            begin
            SELECT count(user_item_number) into max from item where username=NEW.username;
            if max is null then
                max:=1;
            ELSE
                max=max+1;
            end if;
            INSERT INTO item( username, user_item_number, item_value) VALUES (NEW.username,max, NEW.item_value);
            exit;
            exception WHEN unique_violation THEN
            --do nothing
            end;
        end loop;
    return null;
    end;

$$ LANGUAGE 'plpgsql';

CREATE TRIGGER trig1
 before insert 
    ON item
   FOR EACH ROW
EXECUTE PROCEDURE myinsert();

This sulution alows for gaps creation, but it is ok for me.

I wanted to moke trigger instead of insert but it is impossible. So I made before insert trigger and return null. Insert is executed inside procedure. Instruction:

if NEW.user_item_number is not null then return NEW;
    end if;

is to not allow for reccurence

Voyage

Answer 2

It is really hard to generate and maintain such gapless sequence.

the better way to obtain same results is to use window functions to generate such sequences on the fly. Something like:

SELECT id, username, row_number() OVER (PARTITION BY username ORDER BY id) as user_item_number, item_value
from item_table;

It will give you desired results and wont cause any problems with concurrency. Also it will always maintain sequences gapless.

Newfoundland

Your solutions is very smart. But I am affraid that it is a little calcualtion power expensive. I will insert new rows very very rarely and select rows very very often. I am not specialist in databases but I think that it is unnecessary lost of calculation power. I still hope that someone give me example of trigger about which I wrote in my question. – Voyage

@Voyage Any trigger solutions will have concurrency problems in form of duplicates, gaps or serialization errors. – Newfoundland

@Voyage What is the purpose of such id's? Maybe there is a way to solve the problem without them? – Newfoundland

I know another way to solve my problem. I thought that use a trigger is good and easy solution so I asked about it. I don't undesrstood why " Any trigger solutions will have concurrency problems in form of duplicates, gaps or serialization errors". Trigger can trigger any procedure and procedure can catch duplicate eception and increment specific value and insert row again. I do not know to much about procedures and triggers. – Voyage

Answer 3

Auto inc should be unique and only one per table.

So based on what you seem to want

User(User_ID PK, ...)
UserItem(User_Item_ID PK, User_ID FK, ...)
UserItemValue(User_Item_Value_ID PK, User_Item_ID FK, ...)

is where you should be heading from a normalisation point of view

Dorinda

If I right understand your post It is not solution to my problem. I want that every user has got numbered from 1 items. I need it to handle http request. I will select item from db by username and user_item_number. I don't whant use primary key becasue I don't want that any user could guess how many items are in database. – Voyage

In Postgres you can have any number autoincrements per table. – Newfoundland

@Igor Didn't know that, however, I can't think of a design without a flaw that would need more than one – Dorinda

@Mariusz, You can have another unique key / index on the table, by those two columns, don't need to and indeed shouldn't expose an auto inc column. – Dorinda

Answer 4

This should help. A simpler option. Than the message accepted as an answer.

CREATE OR REPLACE FUNCTION set_internal_order() RETURNS trigger as
$$
DECLARE
    max_order INTEGER;
BEGIN
    IF NEW.internal_order IS NOT NULL THEN
        RETURN NEW;
    END IF;

    SELECT COALESCE(MAX(internal_order), 0) + 1 INTO max_order
    FROM landing
    WHERE project_id = NEW.project_id; -- project id is Frghn. key

    NEW.internal_order := max_order;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig1
    before insert
    ON landing
    FOR EACH ROW
EXECUTE PROCEDURE set_internal_order();

Lavallee