CODEWITHSUNDEEP
postgresql
Winfield Trail
Winfield Trail

Reputation: 5695

Sequence field, tracking individual sequences for primary key/sequence pair

I have a table of user-uploaded objects. Each user can have an arbitrary number of objects. I want each object to have a sequential identifier, like so:

USERNAME    OBJECTNAME    OBJID
Kerin        cat            1
Kerin        dog            2
Narcolepsy   pie_tins       1
Kerin        mouse          3

I'd like for OBJID to be a sequence, but tracking the sequence number individually per USERNAME field. I can sort of accomplish this by first querying the DB and SELECTing the highest OBJID and then incrementing that value by one and using it in my INSERT, and that's probably fine because it'd be difficult for a user to run two uploads at once, but the query overhead and the feeling that I'm doing it wrong makes me want to find a better way.

Upvotes: 3

Views: 399

Answers (1)

mu is too short
mu is too short

Reputation: 434845

If you don't need them to be sequential then you could probably get away with adding a PK of type serial (or bigserial) to the table. The numbers would still be unique per-username but it would be dead simple to implement and you wouldn't have the ugliness of UUIDs.

You could create one sequence per username through manual CREATE SEQUENCE calls. Then, you could add a BEFORE INSERT trigger to set the objid by figuring out which sequence to use and then calling nextval on it. If your usernames are limited to the usual /[a-z][a-z0-9]*/ pattern, then you could build the sequence names as something like "seq_objid_username" and the trigger would be able to figure out which sequence to use quite easily; the per-username sequences could be created by an INSERT trigger on your user table. This approach will work and it will be safe because it relies on PostgreSQL's existing transaction-safe sequence system.

Upvotes: 4

Related Questions