Skip to content

ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_ci_pipelines_on_project_id_and_iid"DETAIL: Key (project_id, iid)=([PROJECT_ID], [IID]) already exists.

Problem

In https://gitlab.com/gitlab-com/ops-sub-department/section-ops-request-for-help/-/issues/264 and https://gitlab.com/gitlab-com/ops-sub-department/section-ops-request-for-help/-/issues/293 we are seeing errors due to the IID tracking (handled by InternalId) not reflecting the latest CI Pipeline IID after a project is imported, even though the IID tracking should be flushed after import https://gitlab.com/gitlab-com/ops-sub-department/section-ops-request-for-help/-/issues/264#note_1718285528.

Workaround

A workaround is to reset the IID tracking for CI Pipelines for the project https://gitlab.com/gitlab-com/ops-sub-department/section-ops-request-for-help/-/issues/264#note_1730359496.

To verify if you're affected, check your logs for an error like this:

PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_ci_pipelines_on_project_id_and_iid"
DETAIL:  Key (project_id, iid)=(52897123, 756) already exists.

The project ID of your affected project would be 52897123 in this example. To resolve the issue, connect to the PostgreSQL database and run the following queries:

SELECT * FROM internal_ids WHERE project_id = 52897123 and usage = 5;

SELECT COUNT(*) FROM ci_pipelines WHERE project_id = 52897123;

Make sure to replace 52897123 with your project ID. The value 5 for usage in the first query is a constant and refers to CI pipelines.

The last_value in the response of the first query will differ from the count in the second query response, 349 vs. 8217 in this example:

    id    | project_id | usage | last_value | namespace_id
----------+------------+-------+------------+--------------
 25674456 |   52897123 |     5 |        349 |
(1 row)



 count
-------
  8217
(1 row)

To resolve this, delete the row from the internal_ids table via its ID, 25674456 in this example:

DELETE FROM internal_ids WHERE id = 25674456;

This ID will be recreated when next needed, deleting it poses no risk.

Alternatively, you can use a Rails console session to achieve the same result:

InternalId.flush_records!(usage: :ci_pipelines, project_id: <project_id>)
Edited by Manuel Grabowski