`NextItem` query may not return available items in series order

[gjcolombo]

Nexus's NextItem query fragment allows callers to select an "available" item from a pool of items that may have been previously assigned to some other entity. For example, disk attachment uses NextItem to select an available PCI slot to which to attach a new disk.

Perhaps unintuitively, NextItem makes no guarantees about the order in which it returns eligible items from its pool. For example, here's a mock NextItem query returning the available disk slots for an instance with no disks currently attached:

root@[fd00:1122:3344:107::3]:32221/omicron> SELECT slot FROM
(SELECT 0 + shift AS slot FROM
(SELECT generate_series(0, 8) AS shift UNION ALL SELECT generate_series(0, -1) AS shift)
LEFT OUTER JOIN disk
ON (attach_instance_id, slot, time_deleted IS NULL) = ('97afec53-7d1c-41ef-a583-47521c6ed0c8', 0 + shift, TRUE)
WHERE slot IS NULL);
  slot
--------
     1
     2
     4
     5
     6
     7
     8
     0
     3
(9 rows)

This is weird if you (like me) read NextItem to mean "the next available item in the set, if they are considered in order" and not just "any available item."

We should give NextItem these semantics (i.e. "return the lowest-numbered available item") by sticking an ORDER BY on the outermost SELECT before returning the first row.

Note that this is (as far as I know) only a hygiene issue so far--for VMs, disk and NIC attachment work just fine when you plug a device into slot 1 but not slot 0, and this doesn't cause a single slot to be used for more than one disk/NIC on a given instance. But changing this behavior (or the name of NextItem, I suppose) will make it harder to write code in the future that incorrectly assumes that the query returns items in order.

[morlandi7]

Include PR 5067 in the release notes