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

Answer 2

I had the same need and my solution was pretty much what @Edmund said above.

The only downside I found so far is that since my query looks for the MAX() value of the sequence column, in theory, the number could be re-used if you delete the latest row (= by doing that you decrement the max recorded sequence number) and add a new one.

In some future iteration I will probably create a table dedicated to storing max values of each separate sequence, but I want to do some performance testing on how would that work under a heavy load. I'm effectively replacing native sequences without all their optimisations, which must come with downsides.

---

In my case, I am using the sequence numbers over a lot of columns with different partition columns, so I created a universal function I call from my triggers.

Here is the function. You'll need hstore extension to run it.

CREATE OR REPLACE FUNCTION nssequence_nextval()
RETURNS trigger AS $$
DECLARE
    query text;
    nextval bigint;
BEGIN
    IF TG_NARGS != 2 THEN
        RAISE EXCEPTION '% did not supply nssequence_nextval with the required arguments.', TG_NAME;
    END IF;

    query := 'SELECT COALESCE(MAX(%I)::text, ''0'')::bigint + 1 FROM %I.%I WHERE organisation_id = $1.organisation_id AND %I = $1.%I'::text; -- In my context, every row has an organisation_id as my app is multi-tenant-capable. Adjust this query to your needs.
    EXECUTE format(
        query,
        TG_ARGV[0], -- MAX(%I)::text, the column holding the sequence
        TG_TABLE_SCHEMA, TG_TABLE_NAME, -- FROM %I.%I - the table we are working with
        TG_ARGV[1], TG_ARGV[1] -- %I = $1.%I -- the column with the sequence namespace. Ideally a parent entity's UUID.
    ) USING NEW INTO nextval;

    -- The property name is the first argument of the trigger function, so we need hstore to set it.
    -- See https://stackoverflow.com/questions/7711432/how-to-set-value-of-composite-variable-field-using-dynamic-sql/7782641#7782641
    NEW := NEW #= hstore(TG_ARGV[0]::text, nextval::text);

    RETURN NEW;
END
$$ LANGUAGE 'plpgsql';

and here is the trigger for each column (replace things in square brackets with actual names):

CREATE OR REPLACE TRIGGER [nssequence_trigger_name]
BEFORE INSERT ON [schema.table]
FOR EACH ROW
EXECUTE PROCEDURE nssequence_nextval('[sequence_column_name]', '[partition_column_name]')

Just by the way, I have automated this. I use a column comment on each sequence column, such as nssequence:partition_column_name. I also wrote a script that looks for all columns that have the such comment and adds the triggers to them.

This is the query to find all sequence columns:

SELECT
    DISTINCT pgd.objoid, -- only used for filtering out duplicates
    c.table_schema,
    c.table_name,
    c.column_name,
    pgd.description
FROM pg_catalog.pg_statio_all_tables as st
INNER JOIN pg_catalog.pg_description pgd ON (
    pgd.objoid = st.relid
)
INNER JOIN information_schema.columns c ON (
    pgd.objsubid   = c.ordinal_position
    AND c.table_schema = st.schemaname
    AND c.table_name   = st.relname
)
INNER JOIN information_schema.tables t ON (
    t.table_type = 'BASE TABLE'
)
WHERE
    t.table_schema != 'information_schema'
    AND t.table_schema NOT LIKE 'pg_%'
    AND pgd.description LIKE 'nssequence%'

Finally I also have a cleanup mechanism in place that looks for triggers that are created over columns that no longer exist or no longer say they are a sequence number, but that's probably too much detail for this answer.