Reset project_push_rules id sequence to prevent UniqueViolation

What does this MR do and why?

The project_push_rules table was populated via a sync trigger from push_rules (introduced in !208490 (merged)), which inserts records with explicit IDs. This means the project_push_rules_id_seq sequence was never advanced and remains at a low value.

This MR consists of two post migrations.

1. db/post_migrate/20260224233424_reset_project_push_rules_id_sequence.rb resets the sequence to MAX(id) + 100 to prepare for the upcoming trigger change that will use nextval() for ID generation. The +100 buffer provides a safety margin for any trigger-based inserts that occur between this migration and the trigger update during deployment.

2. db/post_migrate/20260225002111_update_project_push_rules_trigger_to_use_nextval.rb updates the project_push_rules sync trigger to stop bypassing the ID sequence. By using nextval() instead of inserting pre-defined IDs, we ensure the sequence advances automatically with every new record. Discussed here.

References

• Trigger MR: !208490 (merged)
• Issue: #589664

How to set up and validate locally

For db/post_migrate/20260224233424_reset_project_push_rules_id_sequence.rb

rails dbconsole

gitlabhq_development=# SELECT MAX(id) FROM project_push_rules;
 max
-----
  12
(1 row)

gitlabhq_development=# SELECT last_value FROM project_push_rules_id_seq;
 last_value
------------
        112
(1 row)

The sequence value should equal MAX(id) + 100

db/post_migrate/20260225002111_update_project_push_rules_trigger_to_use_nextval.rb

1. Verify inserting a new record to the push_rules table advances the sequence

rails dbconsole

INSERT INTO push_rules (project_id, created_at, updated_at)
VALUES (
  (SELECT id FROM projects WHERE id NOT IN (SELECT project_id FROM push_rules WHERE project_id IS NOT NULL) ORDER BY id LIMIT 1),
  NOW(),
  NOW()
);

 
SELECT last_value FROM project_push_rules_id_seq;.
 last_value
------------
          113
(1 row)

2. Verify updating the existing record with the same project ID doesn't advance the sequence.

SELECT pr.id AS push_rules_id, ppr.id AS project_push_rules_id, pr.project_id
FROM push_rules pr
JOIN project_push_rules ppr ON pr.project_id = ppr.project_id
WHERE pr.id = (SELECT MAX(id) FROM push_rules);

 push_rules_id | project_push_rules_id | project_id
---------------+-----------------------+------------
             4 |                   104 |          5
(1 row)

- Update the push_rules record (IDs are different but the trigger correctly finds the project_push_rules row by project_id)

UPDATE push_rules SET max_file_size = 100 WHERE id = (SELECT MAX(id) FROM push_rules);

SELECT last_value FROM project_push_rules_id_seq; ----> sequence remains the same
 last_value
------------
          113
(1 row)

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.