Support polymorphic association with LooseForeignKeys, async delete notes when vulnerabilities are deleted using it.

The following discussion from !69165 (merged) should be addressed:

  • @ayufan started a discussion: (+2 comments)

    Interesting. I think that answer is negative but worth asking: this does not support polymorphic foreign keys.

Changes required

DB Changes

No changes required to the loose_foreign_keys_deleted_records table

LFK definitions Changes

We'll extend the LFK definition to allow adding conditions for the DELETE/UPDATE queries.
This will allow a more generic solution that can be used not only for cases of polymorphic association.
In the case of a polymorphic association, the association type column and the required value are added to the condition.
The following is an example in the case where vulnerabilities is the parent table and notes is the child table

 notes:
   - table: vulnerabilities
     column: noteable_id
     conditions:
       - column: noteable_type
         value: "Vulnerability"
     on_delete: async_delete

LFK Cleanup Worker changes

Add support to create a query with added 'WHERE` conditions based on the condition in the LFK definitions The query would be like this:

DELETE FROM notes 
WHERE 
noteable_id IN (SELECT id FROM vulnerabilities WHERE id IN (...))
AND noteable_type = "Vulnerability"

Future development

Add support to the generate-loose-foreign-key script to detect polymorphic association when updating config/gitlab_loose_foreign_keys.yml and a condition when adding the new loose foreign key

Edited Nov 12, 2024 by Schmil Monderer
Assignee Loading
Time tracking Loading