I have the following scenario:
A 'task' table that has the fields:
id => primary key, updated on each insert using a sequence
customerid => integer
localid => integer
I need the localid to be sequential and unique per unique customerid. The data needs to look like this:
1, 92, 1
2, 92, 2
3, 93, 1
4, 93, 2
5, 93, 3
6, 92, 3
and so on
I am presently doing this on the INSERT using an INNER SELECT, like this:
INSERT INTO task (id, customerid, localid) VALUES (nextval('task_id'), 92, (SELECT MAX(localid) + 1 FROM task WHERE customerid = 92));
The problem with this query is that if two INSERTS are performed at the same time for the same customerid (which is happening more than you would think), than both records end up with the same localid.
I've already considered:
1. I can't lock the table, because there are too many inserts happening and it will slow down the app.
2. I can't use temporary sequence tables because they only exist per session, and each insert statement belongs to a seperate session.
3. I could create a sequence table for every customerid (i.e. CREATE SEQUENCE task_id_92) but then I would end up with thousands of sequence tables.
4. Sequence tables wont' rollback with the transaction, so any db error would create a gap in the localid sequence. This is not ideal, but I suppose I could live with it (but would prefer not to)
Anyone know a solution for this? This can't be the first time anyone has ever tried to do this. Thanks!
- John