Postgres: Generate 1-per-parent id sequences for child ids with no holes

Question

I want a – can I call it a sub-sequence ? – to generate identity for child lines. A sequence per parent entity, starting at 1 with no holes. That is, each parent's childrens Ids should start at 1 and increment. What is a simple and reliable solution?

Detail, if it helps

• My application inserts rows into a child table for order LineItems.
• The application requires LineItems be numbered consecutively from 1
• The LineItems table itself also has child tables, in this case Variation.
• After insert, I return the inserted row with the new sequential LineItemId
• The app doesn't allow deletions

Current code (the @ symbols marks input parameters):

Insert Into Lineitems (Jobid,Id,Stage, Costprice, Sellprice, Customdescription)
        Values (@JobId,
        -- *Calculate next line id for this jobid*
        @Stage,@Costprice,@Sellprice,@Customdescription)
;
Insert Into Variations (Lineitemjobid,Lineitemid,InstructedBy,InstructionDate,Type)
    Values (@jobId,
           *Use the just-inserted line id for this jobid*
           @Instructedby, @Instructiondate, @Variation_Type)
;
Select * from ...etc... 
Where (JobId,Id)=(@jobId, *Use the just-inserted line id for this jobid*)

The places marked *Calculate...* & *Use...* are where I want my 1-based, no-holes, per-parent, subsequence.

My First Effort

I create a view to get currentval/next val

Create View Lineitemidseq(Jobid, Currentval, Nextval) As
        Select Jobs.Id                    As Jobid,
               Coalesce(Max(L.Id), 0)     As Currentval,
               1 + Coalesce(Max(L.Id), 0) As Nextval
        From Jobs Left Join Lineitems L On Jobs.Id = L.Jobid
        Group By Jobs.Id

And then I can fill in my calculations for next lineid & last-inserted-in-this-session-lineid:

Next : (Select Nextval from Lineitemidseq Where JobId=@jobId)

Last-inserted-in-this-session : (Select Currentval from Lineitemidseq Where JobId=@jobId)

You can see the flaw, albeit at low probability. Because I have a child of the child, if 2 people add lines simultaneously, I could pick up a wrong CurrentVal in statements 2 & 3.

So:

• Presumably I could fix this with an explicit lock for the 3-line transaction. Is there a better way?
• If not, what is a reasonably minimalist lock that would provide correctness for such a small transaction?

Answer 1

So the simplest thing I can see is to put the 3 lines inside:

Begin Transaction Isolation Level REPEATABLE READ
;
Insert ...
Insert ...
Select ...
;
Commit

Repeatable Read is the level that guarantees that

(Select ... From Lineitemidseq Where JobId=@jobId)

in statements 2 and 3 is consistent with

(Select ... From Lineitemidseq Where JobId=@jobId)

in statement 1.

Where “consistent” means, unaffected by any concurrent transaction.

This amount of locking seems like the bare minimum (or close to) that can do the job?