[LRUG] Scoping sequences to a parent

[Tom Stuart]

On 2 Sep 2016, at 10:49, Andrew Stewart <boss at airbladesoftware.com> wrote:
>> Is it impractical to just use an autoincrementing column (as you suggest) to give the children an explicit ordering, but then “number” them by their derived index rather than the concrete value of that column?
> I suppose it depends on whether the number can be derived in an efficient, lock-free way.

If it’s just the index of the child within the ordered collection of children then I’d expect that to be fine. The locking behaviour depends on what autoincrement lock mode you’re using, but hopefully that is already working.

Your subsequent email suggests that you want to actually store this value against the child rather than derive it, which isn’t quite what I meant, but it should be doable. Once the autoincremented ID has been assigned, the child index is fixed (i.e. you can safely assign it with an UPDATE whenever is convenient) as long as you don’t allow previous children to be deleted.