atomic insert select operation 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 &amp; issue_id_max mapping.
project_issues_max:
-----------------
project_id issue_id
P1            1
P2            1
...
P3            2

&amp; then having something like:
prefix_id -&gt; 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 = &quot;some project id (p1)&quot;
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.
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 --&lt;&lt; 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 &quot;generator&quot; 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 &quot;generator row&quot; 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 &quot;gapless sequence&quot;.
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