0

So i have two tables:

Project:
----------
project_id (PK)
project_prefix

Issues:
----------
issue_id
project_id (FK)

for every project with some prefix ex P1 i want all issues to be like P1-1 P1-2... P3-1 P3-2, whenver an issues is created in the issue table.

The way that i can think of doing this is maintaining a thrid table with Project id & issue_id_max mapping.

project_issues_max:
-----------------
project_id issue_id
P1            1
P2            1
...
P3            2

& then having something like:

prefix_id -> fetch_project_prefix(p1)

BEGIN
    Insert case values( 
        concat(
            prefix , select( 
                        coalesce(
                             max (project_issues_max.case_id) + 1 ,0)
                             from project_case_max 
                        )
                    )
            )
        )
    update project_case_max set case_id = case_id + 1 where project_case_max.project_id = "some project id (p1)"
COMMIT

I am sure this doing max()+1 will have concurrency issues with multiple simultaneous session reads of case_id value.

What is the best way to do this?, This question here is almost similar and seems to be accepted.what makes doing max()+1 safe by adding SKIP LOCKED. It also handles ON CONFLICT, so, what i can infer is conflict can happen only when multiple session read the same value, then how come its race condition safe.

5
  • 1
    Why would you need that? Just make issue_id an identity column (in Postgres). I don't see any reason for complicating things.
    – user1822
    Commented Sep 3, 2020 at 19:57
  • @a_horse_with_no_name I am assuming by identify column you mean a sequence type..but this is part of the spec..the ids for issues needs to be attached with projects ids
    – anekix
    Commented Sep 3, 2020 at 20:02
  • 1
    No offense, but that's a really stupid requirement. You can always generate something like that when retrieving the issues. So the issue_id is a varchar column? The project_prefix would need to be defined as unique as well in addition to the project_id column - which seems like quite an overhead.
    – user1822
    Commented Sep 3, 2020 at 20:07
  • @a_horse_with_no_name yes i wanted to go with the approach of getting in that format while retrieving . but consider this...issue 1 in project 1 with prefix P1 inserted , i get the issue id as P1-1 ..good until now, then issue 2 inserted under project 2 with prefix P2, i get issue id P2-2 (since issue_id is an identity column) while it should be P2-1 as its the first issue of project P2
    – anekix
    Commented Sep 3, 2020 at 20:13
  • @a_horse_with_no_name the idea is for users to easily see project ids with incremental issue ids that they create, much like jira. P1 (project 1) P1-1 P1-2 P1-3 P2 (project 2) P2-1 internally i can use autoincrement type to map projects to issues but that isnt sufficient to get results like this
    – anekix
    Commented Sep 3, 2020 at 20:24

1 Answer 1

2

I think requirements like that stem from a fundamental misconception on how relational databases work and what a primary key should do.

Anyhow, in Postgres I would do it like this:

You indeed need a separate table to store the last generated id for each project prefix. To make that a unique value, this means that the project prefix also has to be unique.

The two base tables:

create table project 
(
  project_id     integer primary key, 
  project_prefix varchar(20) not null unique --<< unique is important!
);

create table issue 
(
  issue_id   varchar(50) primary key, -- this needs to be longer than the prefix!
  project_id integer not null references project,
  severity   integer not null, 
  title      text not null,
  constraint check_severity check (severity between 1 and 10)
);

The "generator" table could look like this:

create table id_generator
(
   prefix    varchar(20) not null primary key,
   issue_id  integer     not null default 1
   foreign key (prefix) references project (project_prefix)
);

Then we an create a function that generates the next issue id based on the prefix:

create or replace function generate_issue_id(p_prefix text)
  returns text
as
$$
  insert into id_generator(prefix)
  values (p_prefix)
  on conflict (prefix) do update 
    set issue_id = id_generator.issue_id + 1
  returning concat(prefix, '-', issue_id);
$$
language sql;

Then we need to create a trigger for the issue table to automatically assign the issue_id. That way we don't have to provide the project prefix during insert.

create function assign_issue_id()
  returns trigger
as
$$
begin
  select generate_issue_id(project_prefix)
    into new.issue_id
  from project
  where project_id = new.project_id;
  return new;
end;
$$
language plpgsql;

create trigger issue_trigger
  before insert on issue
  for each row
  execute function assign_issue_id();

This works because the function will lock the "generator row" for the project for which the issue ID was generated. The lock will be held until the transaction is committed.

This will effectively serialize inserts into the issue table for a one project, i.e. if a second insert happens before the first one is committed (or rolled back) the second insert has to wait. As the increment of the project specific ID is committed together with the insert into the issue table, a rollback will also rollback the issue id increment, effectively making this a "gapless sequence".

The insert into the issue table then should not provide a issue_id as it is generated automatically:

insert into project (project_id, project_prefix) values (1, 'p1');
insert into issue (project_id, severity, title) 
values 
(1, 1, 'Not working'),
(1, 10, 'Wrong translation');

Online example

1
  • Thanks for such a detailed answer :)
    – anekix
    Commented Sep 3, 2020 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.