NASA-AMMOS
External plan uploads could trigger uniqueness violation on serial PK generator
Background
The addition of activity anchoring means that plans uploaded via the API either need to re-use the existing activity directive IDs as PKs (to preserve anchoring) or have all anchor IDs updated to match new directive IDs in the plan.
If a plan is uploaded with activity directive PKs that are larger than the state of the directive table serial ID generator, then it's possible that a user can encounter a uniqueness violation when trying to add activities to that plan in the future.
Steps to Reproduce
- Create a plan with a single directive whose ID is generated by the serial ID generator in the database. Say that the ID of this directive is 1.
- Via the API, add another directive to this plan, manually setting the ID to 3.
- Via the UI, add one directive to the plan. The generator will assign ID 2 and the directive is created successfully.
- Via the UI, try to add one more directive to the plan. The API call returns a uniqueness violation and the directive is not created.
- Via the UI, try to add one more directive. The generator state has been incremented, so the directive is assigned ID 4 and is created successfully.
Actions
I don't need this resolved as I've already implemented a solution that uses the serial ID generator and re-maps anchored activities; I'm writing this ticket for awareness and documentation.
To add a bit of context - as of the addition of the branch/merge feature, it is now possible to use the API to upload new activities with user-specified ids.
As of the addition of the relatively scheduled activities feature, it now takes two API calls to bulk upload a plan - one call to insert activities, and another call to wire up the anchor_ids.
Carter and I brainstormed a possible way to bulk upload a plan with a single API call - leveraging the ability to upload user-specified ids. However, we weren't confident that this ability is intended to be used, and what the possible consequences might be of uploading an activity with a custom id. Indeed, as Carter describes above, it turns out that if your custom id is larger than the largest generated id, it creates the possibility that a future activity insert will error out with a uniqueness violation.
An open question is - what should the guidelines be around uploading activities with user-specified ids? Some options are:
- Disallow this - it's not an intended feature
- Document this as "use at your own risk"
- Find a way to make this feature safer to use
Another open question is - what should the guidelines be around bulk uploading a plan with relatively scheduled activities?
I'll amend that bulk uploading isn't quite as simple as inserting the activities then connecting the anchor IDs. Anchor IDs are registered as foreign keys, so you can't insert an activity directive if its anchored to a directive that hasn't yet been added, so the process is more like a preorder traversal (unless you track & pull anchor IDs, then re-assign them -- maybe I'll do that...)
if your custom id is larger than the largest generated id, it creates the possibility that a future activity insert will error out with a uniqueness violation.
What if the future activity insert was smart and avoided the violation? Is that not a better solution? If it's a matter of efficiency, there still must be an efficient way to quickly identify an unused ID--it just might be unusual, such as a trigger that updates the "set" of available IDs whenever a directive is inserted.
So, a solution to this could be that the id is optional when inserting a directive. If specified and non-unique, it fails with the violation. If not specified, a guaranteed unique id is generated. There are use cases where specifying the directive id is desirable, and currently this isn't possible in the UI or aerie-cli.
Theresa explained elsewhere that the directive IDs are shared in plan branch/merge such that they need to be unique across plans. Given that, the IDs included in a plan download would not be usable if the plan were edited and uploaded, and a user shouldn't expect to be able to create their own IDs. Thus, the solution above wouldn't be good enough.