[LRUG] Scoping sequences to a parent

[Andrew Stewart]

> I'm not surprised you're having deadlocks, I assume you're using those columns as a composite primary key which would mean the whole table is going lock while you figure out the next id in the sequence.
> 
> I wonder if it would be practical for you to just emulate this behaviour by having a regular auto increment sequential ID for the whole table and a regular foreign key to the parent then to find 69/48 you could do something like:
> 
> `SELECT * FROM things WHERE parent_id = 69 ORDER BY id OFFSET 47 LIMIT 1`
> 
> I'm sure this has plenty of issues but it would remove a bottle neck and solve a potential tricky consistency problem.
> 
> I hope there's not an off by one error in there :D

Sounds good, though I'd like to store the number/offset/count in the record itself.  I use this number as part of a "friendly" id or candidate key which I show to the user, and people need to be able to search by that id.