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
?
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.