atomic insert select operation
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