Postgres Dynamically Create Sequences

Question

I'm writing an application where there are multiple users are users can upload reports within the application.

Currently, I have a 'reports' table that holds all of the submitted reports which has an 'id' field that is a serial primary key on the table.

The requirements I have specify that users need to be able to specify a prefix and a number for their reports to start counting from. For example, a user should be able to say that their reports start at ABC-100, then the next one is ABC-101, ABC-102, and so on and so forth.

The way I'm thinking of achieving this is that when a user creates an account, he can specify the prefix and start number, and I will create a postgres sequence with the name of the prefix that is specified and a minValue of the number the user wants reports to start at.

Then when a user submits a new report, I can mark the report_number as nextval(prefix_sequence). In theory this will work, but I am pretty new to postgres, and I want some advice and feedback on if this is a good use of sequences or if there is a better way.

Answer 1

This is an area where you probably don't need the key benefit of sequences - that they can be used concurrently by multiple transactions. You may also not want the corresponding downside, that gaps in sequences are normal. It's quite normal to get output like 1, 2, 4, 7, 8, 12, ... if you have concurrent transactions, rollbacks, etc.

In this case you are much better off with a counter. When a user creates an account, create a row in an account_sequences table like (account_id, counter). Do not store it in the main table of accounts, because you'll be locking and updating it a lot, and you want to minimise VACUUM workload.

e.g.

CREATE TABLE account_sequences
(
    account_id integer PRIMARY KEY REFERENCES account(id),
    counter integer NOT NULL DEFAULT 1,
);

Now write a simple LANGUAGE SQL function like

CREATE OR REPLACE FUNCTION account_get_next_id(integer)
RETURNS integer VOLATILE LANGUAGE sql AS
$$
UPDATE account_sequences
SET counter = counter + 1
WHERE account_id = $1
RETURNING counter
$$;

You can then use this in place of nextval. This will work because each transaction that UPDATEs the relevant account_sequences row takes a lock on the row that it holds until it commits or rolls back. Other transactions that try to get IDs for the same account will wait for it to finish.

For more info search for "postgresql gapless sequence".

If you want, you can make your SQL function fetch the prefix too, concatenate it with the generated value using format, and return a text result. This will be easier if you put the prefix text NOT NULL column into your account_sequences table, so you can do something like:

CREATE OR REPLACE FUNCTION account_get_next_id(integer)
RETURNS text VOLATILE LANGUAGE sql AS
$$
UPDATE account_sequences
SET counter = counter + 1
WHERE account_id = $1
RETURNING format('%s%s', prefix, counter)
$$;

By the way, do not take the naïve approach of using a subquery with SELECT max(id) .... It's completely concurrency-unsafe, it'll produce wrong results or errors if multiple transactions run at once. Plus it's slow.