3

In my app, there are organizations that can create tasks. Instead of having a serial task ID, I would like to have an incrementing task ID for each organization ID. Just like in GitHub, where repo + issue number identifies the issue. Tasks look like this:

create table "Tasks" (
  "organizationId" int references "Organizations"(id),
  id int check (id > 0),
  description text not null,
  
  primary key ("organizationId", id)
);

Users should not "know" the ID when creating a task. How to set the ID automatically for each task?

I'm using PostgreSQL with PostgREST as an API. The solution ideally allows me to use PostgREST's CRUD APIs as is.

I've tried a before insert trigger like this.

create function increment_task_id() returns trigger language
plpgsql security definer as $$
declare
  next_id integer := 1;
begin
  select coalesce(max("id") + 1, 1)
    from "Tasks"
  where "organizationId" = new."organizationId"
  into next_id;
  new.id = next_id;
return new;
end;$$;

create trigger task_insert
before insert on "Tasks"
for each row execute function increment_task_id();

This works fine until I run multiple insertions in parallel. The triggers seem to be executed in parallel, leading to some tasks failing the primary key constraint.

How can I avoid this? Is there a better way?

ccssmnn
  • 306
  • 4
  • 11
  • In a multi-user environment ( or insert in parallel) assigning the key via MAX+1 is a virtual guarantee you will get duplicates. This results from the second user (insert) getting the same max value when it does `select max` before the first commits. The solution use a sequence (or better `generated always as`). *Postgres guarantees never to generate the same value* (unless you reset it). But you must be ready to accept gaps in the numbering. – Belayer Jan 23 '23 at 02:51
  • 2
    Hmm, I'm sure you have thought about this as well, but one thing you can do is to loop on the client side until the insert succedes. I know it's not the prettiest solution, but it gets the job done. – dshukertjr Jan 23 '23 at 09:43
  • Additionally to what @dshukertjr said, the error code returned when a PK already exists is `23505`, so you could use that to help in the loop client side. Perhaps it could also be looped inside the trigger (handling the error). – Laurence Isla Jan 24 '23 at 19:19
  • @dshukertjr I like pragmatic solutions. The insert might take twice as long (or more) but only in the quite rare event of inserts at the very same time. I'm still looking for the Database solution to this problem. – ccssmnn Jan 24 '23 at 20:55
  • @Belayer I would need to create one sequence for every organization in every table and find the right sequence from the trigger. Sounds quite scary to me. – ccssmnn Jan 24 '23 at 21:01
  • No you do not, just create 1 sequence. That is a generated value and has exactly 1 purpose - *be unique in the table*. It has nothing to do with any other column and serves no other purpose. Then make that single sequence the default (or batter use generated always as identity) and the PK and eliminate the trigger altogether. Especially since you already know the organization via the "organizationId" foreign key. – Belayer Jan 24 '23 at 23:45
  • Then the requirement of an incrementing task ID for each organization ID is not fulfilled – ccssmnn Jan 25 '23 at 19:06

0 Answers0