How to create a PostgreSQL partitioned sequence?

Question

Is there a simple (ie. non-hacky) and race-condition free way to create a partitioned sequence in PostgreSQL. Example:

Using a normal sequence in Issue:

| Project_ID | Issue |
| 1          | 1     |
| 1          | 2     |
| 2          | 3     |
| 2          | 4     |

Using a partitioned sequence in Issue:

| Project_ID | Issue |
| 1          | 1     |
| 1          | 2     |
| 2          | 1     |
| 2          | 2     |

Answer 1

I do not believe there is a simple way that is as easy as regular sequences, because:

1. A sequence stores only one number stream (next value, etc.). You want one for each partition.
2. Sequences have special handling that bypasses the current transaction (to avoid the race condition). It is hard to replicate this at the SQL or PL/pgSQL level without using tricks like dblink.
3. The DEFAULT column property can use a simple expression or a function call like nextval('myseq'); but it cannot refer to other columns to inform the function which stream the value should come from.

You can make something that works, but you probably won't think it simple. Addressing the above problems in turn:

1. Use a table to store the next value for all partitions, with a schema like multiseq (partition_id, next_val).

2. Write a multinextval(seq_table, partition_id) function that does something like the following:

   1. Create a new transaction independent on the current transaction (one way of doing this is through dblink; I believe some other server languages can do it more easily).
   2. Lock the table mentioned in seq_table.
   3. Update the row where the partition id is partition_id, with an incremented value. (Or insert a new row with value 2 if there is no existing one.)
   4. Commit that transaction and return the previous stored id (or 1).

3. Create an insert trigger on your projects table that uses a call to multinextval('projects_table', NEW.Project_ID) for insertions.

I have not used this entire plan myself, but I have tried something similar to each step individually. Examples of the multinextval function and the trigger can be provided if you want to attempt this...