(Part 1) Partition Manager to add LFK triggers to Partitions

What does this MR do and why?

Partitioned tables that have LFK trigger should also add a customized version of this trigger to the partitions as well.

This MR is Part 1 of #507343 (closed)

This will be followed by a Part 2 MR !176031 (merged) which is going to add the LFK trigger to existing CI Partitions.

Validate locally

Validate triggers creation

1. in Rails console run the following

FactoryBot.create(:ci_partition, id: 103, status: 0)
Feature.enable(:ci_partitioning_automation)
Gitlab::Database::Partitioning::PartitionManager.new(CommitStatus, connection: CommitStatus.connection).sync_partitions

2. Validate that a new partition has been added to p_ci_builds in psql using the command \d+ p_ci_builds. You need to connect to the CI database using gdk psql -d gitlabhq_development_ci
3. Validate that the new partition has an LFK trigger using the command \d gitlab_partitions_dynamic.ci_builds_103. It should have something like

ci_builds_103_loose_fk_trigger AFTER DELETE ON gitlab_partitions_dynamic.ci_builds_103 REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records_override_table('p_ci_builds')

Validate LFK items

1. Stop Sidekiq workers gdk stop rails-background-jobs.
2. Clean up the LFK table delete from loose_foreign_keys_deleted_records from psql.
3. Create a CI Build object in the new partition (maybe there is an easier way)

ci_build = FactoryBot.create(:ci_build, pipeline: Ci::Pipeline.last, project: Project.last, user: User.first)
ci_build.stage_id = nil
ci_build.partition_id = 103
ci_build.save(validate: false)

4. Run this from psql on the CI database delete from gitlab_partitions_dynamic.ci_builds_103;
5. select * from loose_foreign_keys_deleted_records; Should show you the deleted record in the LFK table. Something like this

   id    | partition | primary_key_value | status |          created_at           | fully_qualified_table_name |         consume_after         | cleanup_attempts 
---------+-----------+-------------------+--------+-------------------------------+----------------------------+-------------------------------+------------------
 1300034 |         4 |           1300019 |      1 | 2025-01-27 16:58:44.684629+00 | public.p_ci_builds         | 2025-01-27 16:58:44.684629+00 |                0
(1 row)

Clean up

1. DROP TABLE gitlab_partitions_dynamic.ci_builds_103
2. DELETE FROM ci_partitions where id = 103