The problem was that more than 50k entries were added in results_propagate - in the before_delete_items_items trigger - when a relation in items_items was deleted (all the results with item_id=OLD.parent_item_id. This made the updateItem service timeout.
Other cases with the same issue that were not fixed
We have other triggers that add rows into results_propagate, like the before_delete_items_items used to:
after_insert_items_items trigger: a row in results_propagate is added for each result with item_id=NEW.child_item_id. This happen if we add an item with many results as a child of another item.
after_update_items_items: it's the same. So it'll happen if the propagation fields on items_items are updated, for each result with item_id=NEW.child_item_id
How to fix
We can use the same fix as before_delete_items_items.
Additional possible problems in triggers
Other problems might arrive from triggers. Here's a list with a rough explanation of the number of inserts that happens. I can be wrong analyzing the queries since I never touched those parts.
Into results_propagate:
after_insert_groups_groups: one insert for each item_id for which the NEW.child_group_id has a result. There's also a check on permissions that might influence the worst case, I'm not sure but it might be "the group has at least a parent that can be viewed, and no children that can be viewed".
after_update_groups_groups: same as after_insert_groups_groups
after_insert_permissions_generated: one insert for each results of children items of the permission item_id, where participant_id is a child of the permission group_id
after_update_permissions_generated: same as after_insert_permissions_generated
Into permissions_propagate:
after_insert_items_items one insert for each entry in permissions_generated with item_id=NEW.parent_item_id
after_update_items_items: same as after_insert_items_items
Where the issue was fixed
We fixed an issue when a relation between items is deleted in this PR: https://github.com/France-ioi/AlgoreaBackend/pull/1066
The problem was that more than 50k entries were added in
results_propagate- in thebefore_delete_items_itemstrigger - when a relation initems_itemswas deleted (all the results withitem_id=OLD.parent_item_id. This made theupdateItemservice timeout.Other cases with the same issue that were not fixed
We have other triggers that add rows into
results_propagate, like thebefore_delete_items_itemsused to:after_insert_items_itemstrigger: a row inresults_propagateis added for eachresultwithitem_id=NEW.child_item_id. This happen if we add an item with many results as a child of another item.after_update_items_items: it's the same. So it'll happen if the propagation fields onitems_itemsare updated, for eachresultwithitem_id=NEW.child_item_idHow to fix
We can use the same fix as
before_delete_items_items.Additional possible problems in triggers
Other problems might arrive from triggers. Here's a list with a rough explanation of the number of inserts that happens. I can be wrong analyzing the queries since I never touched those parts.
Into
results_propagate:after_insert_groups_groups: one insert for eachitem_idfor which the NEW.child_group_idhas a result. There's also a check on permissions that might influence the worst case, I'm not sure but it might be "the group has at least a parent that can be viewed, and no children that can be viewed".after_update_groups_groups: same asafter_insert_groups_groupsafter_insert_permissions_generated: one insert for eachresultsof children items of the permissionitem_id, whereparticipant_idis a child of the permissiongroup_idafter_update_permissions_generated: same asafter_insert_permissions_generatedInto
permissions_propagate:after_insert_items_itemsone insert for each entry inpermissions_generatedwithitem_id=NEW.parent_item_idafter_update_items_items: same asafter_insert_items_items