How to create a partitioned PostgreSQL sequence?

Question

Is there a simple (i.e. non-hacks) and race-free way to create a partitioned sequence in PostgreSQL. Example:

Using normal sequence in release:

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

Using a partitioned sequence in a release:

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

Answer 1

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

• A sequence stores only one number stream (next value, etc.). You want one for each section.
• , ( ). SQL PL/pgSQL , dblink.
• DEFAULT , nextval('myseq'); , , .

-, , , , . :

• multiseq (partition_id, next_val).

• multinextval(seq_table, partition_id), :

  • , ( dblink, , ).
  • , seq_table.
  • , partition_id, . ( 2, .)
  • ( 1).

• insert , multinextval('projects_table', NEW.Project_ID) .

, - . multinextval , ...