sqlpostgresql

Postgres: "secondary" primary key


How would one have dynamic "secondary" keys? For example, how we have it in Jira. We have a project name, for example "Test" with the shortcut "TEST". All associated tickets in this project are given a ID "TEST-1234", "TEST-1235", and so on.

How would one model this in Postgres?

In my mind, I worked out a table project with name VARCHAR(xx) and short VARCHAR(8). The ticket table would somehow look like this:

CREATE TABLE ticket 
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id INTEGER NOT NULL,
    identifier INTEGER ???,
    FOREIGN KEY (project_id) REFERENCES project(id),
)

How would you construct an identifier that is incrementing for every new ticket that is referencing the same project_id?


Solution

  • These are examples from the older thread I suggested, adjusted to your schema:
    demo1 at db<>fiddle

    create table ticket_id_per_project(
      project_id INT PRIMARY KEY REFERENCES project(id),
      max_ticket_id INT NOT NULL DEFAULT 1);
    
    create function f_ticket_ids()returns trigger language plpgsql as $f$
    begin
        insert into ticket_id_per_project as f
        values (new.project_id,1)
        on conflict (project_id) do update
        set max_ticket_id=f.max_ticket_id+1
        returning format('PROJECT_%s_TICKET_%s',project_id,max_ticket_id)
        into new.identifier;
        
        return new;
    end $f$;
    
    create trigger t_ticket_ids before insert on ticket
    for each row execute function f_ticket_ids();
    

    It makes no attempt at removing gaps or correcting manually broken sequences - if you remove a ticket, you're left with a gap there. Also, all concurrent insert into ticket attempts for the same project, have to wait for predecessors to finish - the delay is negligible but non-zero. Example with brand-new projects and fresh tickets:

    with gen_projects as (
      insert into project 
      select from generate_series(1,3) 
      returning *)
    insert into ticket(project_id)
    select id from gen_projects
    returning *;
    
    id project_id identifier
    1 1 PROJECT_1_TICKET_1
    2 2 PROJECT_2_TICKET_1
    3 3 PROJECT_3_TICKET_1

    The ticket counter continues for each existing project individually:

    with pick_projects as (
      select id from project tablesample bernoulli(50)repeatable(.42)limit 2)
    insert into ticket(project_id)
    select id from pick_projects cross join lateral generate_series(1,3)
    returning *;
    
    id project_id identifier
    4 2 PROJECT_2_TICKET_2
    5 3 PROJECT_3_TICKET_2
    6 2 PROJECT_2_TICKET_3
    7 3 PROJECT_3_TICKET_3
    8 2 PROJECT_2_TICKET_4
    9 3 PROJECT_3_TICKET_4

    You can trade that for only locking if they all insert the first ticket for a given project. Otherwise, concurrently inserted tickets for the same project don't block each other:
    demo2 at db<>fiddle

    create function seeded_sequence_nextval(seed text) returns int as $f$
    declare sequence_name text:='seeded_sequence_'||seed;
    begin 
      begin execute format('create sequence if not exists %I;',sequence_name);
      exception when /*23505*/unique_violation then null;--concurrent create if not exists
      end;
      return nextval(format('%I',sequence_name));
    end $f$ language plpgsql;
    
    create or replace function f_ticket_ids()returns trigger language plpgsql as $f$
    begin
        select format( 'PROJECT_%s_TICKET_%s'
                      ,new.project_id
                      ,seeded_sequence_nextval('tickets_project_'||new.project_id)) 
        into new.identifier;
        return new;
    end $f$;
    

    The function was swapped in-place, so there's no need to redefine the trigger. Works just the same, except for the locking.