PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. 0

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

    Per-project serials are subject to race conditions. The best solution depends on the complete situation.

    One way: every session that wants to write a new a ticket row has to lock the parent table project first, to get (limited) exclusive write access for just the given project. This way you eliminate race conditions without blocking the whole ticket table.

    Like:

    BEGIN;
SELECT FROM parent
WHERE  parent_id = 123
FOR    NO KEY UPDATE;

INSERT INTO ticket
      (project_id, identifier)
SELECT 123       , COALESCE(max(identifier) + 1, 1)
FROM   ticket
WHERE  project_id = 123;
COMMIT;

    The subquery always returns a row due to the aggregate function. So this even works for the first row per project, starting with 1.

    Login or Signup to reply.
  2. 0

    You could do this with a relative counter like so

    1) create a counter table like this one :

    CREATE TABLE t_counter 
   (table_name VARCHAR(63), 
    item_name VARCHAR(32), 
    id_value INT, 
    PRIMARY KEY (table_name, item_name));

    2) create a function to increase the counter and give a new value :

    CREATE OR REPLACE FUNCTION get_new_id 
   (tbl VARCHAR(63), 
    itm VARCHAR(32))
 RETURNS int
AS '
DECLARE n int;
BEGIN
    IF NOT EXISTS(SELECT *
                  FROM   t_counter 
                  WHERE  table_name = tbl
                         AND item_name = itm)
    THEN 
       INSERT INTO t_counter 
       VALUES (tbl, itm, 0);
       n := 1;
    ELSE
       UPDATE t_counter
       SET    id_value = id_value + 1
       WHERE  table_name = tbl
              AND  item_name = itm
       RETURNING id_value + 1 INTO n;
    END IF;
    RETURN n;
END ; '
LANGUAGE plpgsql;

    3) use it in queries like :

    SELECT get_new_id('ticket', 'TEST')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search