PostgreSQL unique constraint violations on downstream pipeline trigger jobs with CI/CD components

Summary

We are using mutiple pipeline configurations which trigger parent-child pipelines using trigger jobs.
Sometimes, these trigger jobs fail with data integrity failure.
All parent and parent-child pipelines use CI/CD components extensively.

It seems that the "data integrity failures" are caused by unique constraint violations (see logs).

Steps to reproduce

1. Create various CI/CD components.
2. Configure parent pipeline (using CI/CD components) triggering multiple parent-child pipelines (also using CI/CD components).
3. Run parent pipeline multiple times (non-deterministic failure).

What is the current bug behavior?

Trigger jobs will fail with data integrity failures occasionally.

Re-running the failed trigger jobs will trigger the parent-child pipelines.
The retry-mechanism provided by GitLab CI/CD cannot be applied to trigger jobs, therefore the jobs cannot be retried automatically.

What is the expected correct behavior?

Trigger jobs should always succeed without any errors.

Relevant logs and/or screenshots

PostgreSQL

2025-02-24_07:59:47.14424 ERROR:  duplicate key value violates unique constraint "p_catalog_resource_component__component_id_used_by_project_idx2"
2025-02-24_07:59:47.14426 DETAIL:  Key (component_id, used_by_project_id, used_date)=(114, 13, 2025-02-24) already exists.
2025-02-24_07:59:47.14426 STATEMENT:  /*application:sidekiq,correlation_id:01JMVFG5F69XPSKN2JCV55NPBV,jid:ae272a27af8710edc9102511,endpoint_id:Ci::CreateDownstreamPipelineWorker,db_config_database:gitlabhq_production,db_config_name:main*/ INSERT INTO "p_catalog_resource_component_usages" ("component_id", "catalog_resource_id", "project_id", "used_by_project_id", "used_date") VALUES (114, 3, 129, 13, '2025-02-24') RETURNING "id"
2025-02-24_07:59:47.33396 ERROR:  duplicate key value violates unique constraint "p_catalog_resource_component__component_id_used_by_project_idx2"
2025-02-24_07:59:47.33397 DETAIL:  Key (component_id, used_by_project_id, used_date)=(126, 13, 2025-02-24) already exists.
2025-02-24_07:59:47.33397 STATEMENT:  /*application:sidekiq,correlation_id:01JMVFG5F69XPSKN2JCV55NPBV,jid:db81dd0e54e2f7d284c84d9c,endpoint_id:Ci::CreateDownstreamPipelineWorker,db_config_database:gitlabhq_production,db_config_name:main*/ INSERT INTO "p_catalog_resource_component_usages" ("component_id", "catalog_resource_id", "project_id", "used_by_project_id", "used_date") VALUES (126, 2, 130, 13, '2025-02-24') RETURNING "id"
2025-02-24_07:59:47.33457 ERROR:  duplicate key value violates unique constraint "p_catalog_resource_component__component_id_used_by_project_idx2"
2025-02-24_07:59:47.33458 DETAIL:  Key (component_id, used_by_project_id, used_date)=(126, 13, 2025-02-24) already exists.
2025-02-24_07:59:47.33458 STATEMENT:  /*application:sidekiq,correlation_id:01JMVFG5F69XPSKN2JCV55NPBV,jid:05af2b55d6cda1ad1d84ab36,endpoint_id:Ci::CreateDownstreamPipelineWorker,db_config_database:gitlabhq_production,db_config_name:main*/ INSERT INTO "p_catalog_resource_component_usages" ("component_id", "catalog_resource_id", "project_id", "used_by_project_id", "used_date") VALUES (126, 2, 130, 13, '2025-02-24') RETURNING "id"

Output of checks

Results of GitLab environment info

Expand for output related to GitLab environment info

System information
System:		Debian 12
Current User:	git
Using RVM:	no
Ruby Version:	3.2.5
Gem Version:	3.6.3
Bundler Version:2.5.11
Rake Version:	13.0.6
Redis Version:	7.0.15
Sidekiq Version:7.2.4
Go Version:	unknown

GitLab information
Version:	17.9.0
Revision:	0e6185dc2b1
Directory:	/opt/gitlab/embedded/service/gitlab-rails
DB Adapter:	PostgreSQL
DB Version:	14.15
URL:		https://[REDACTED]
HTTP Clone URL:	https://[REDACTED]/some-group/some-project.git
SSH Clone URL:	git@[REDACTED]:some-group/some-project.git
Using LDAP:	no
Using Omniauth:	yes
Omniauth Providers: 

GitLab Shell
Version:	14.40.0
Repository storages:
- default: 	unix:/var/opt/gitlab/gitaly/gitaly.socket
GitLab Shell path:		/opt/gitlab/embedded/service/gitlab-shell

Gitaly
- default Address: 	unix:/var/opt/gitlab/gitaly/gitaly.socket
- default Version: 	17.9.0
- default Git Version: 	2.47.2

Results of GitLab application Check

Expand for output related to the GitLab application check

Checking GitLab subtasks ...
Checking GitLab Shell ...
GitLab Shell: ... GitLab Shell version >= 14.40.0 ? ... OK (14.40.0)
Running /opt/gitlab/embedded/service/gitlab-shell/bin/gitlab-shell-check
Internal API available: OK
Redis available via internal API: OK
gitlab-shell self-check successful
Checking GitLab Shell ... Finished
Checking Gitaly ...
Gitaly: ... default ... OK
Checking Gitaly ... Finished
Checking Sidekiq ...
Sidekiq: ... Running? ... yes
Number of Sidekiq processes (cluster/worker) ... 1/1
Checking Sidekiq ... Finished
Checking Incoming Email ...
Incoming Email: ... Reply by email is disabled in config/gitlab.yml
Checking Incoming Email ... Finished
Checking LDAP ...
LDAP: ... LDAP is disabled in config/gitlab.yml
Checking LDAP ... Finished
Checking GitLab App ...
Database config exists? ... yes
Tables are truncated? ... skipped
All migrations up? ... yes
Database contains orphaned GroupMembers? ... no
GitLab config exists? ... yes
GitLab config up to date? ... yes
Cable config exists? ... yes
Resque config exists? ... yes
Log directory writable? ... yes
Tmp directory writable? ... yes
Uploads directory exists? ... yes
Uploads directory has correct permissions? ... yes
Uploads directory tmp has correct permissions? ... yes
Systemd unit files or init script exist? ... skipped (omnibus-gitlab has neither init script nor systemd units)
Systemd unit files or init script up-to-date? ... skipped (omnibus-gitlab has neither init script nor systemd units)
Projects have namespace: ...
[ALL PAIRS] ... yes
Redis version >= 6.2.14? ... yes
Ruby version >= 3.0.6 ? ... yes (3.2.5)
Git user has default SSH configuration? ... yes
Active users: ... 20
Is authorized keys file accessible? ... skipped (authorized keys not enabled)
GitLab configured to store new projects in hashed storage? ... yes
All projects are in hashed storage? ... yes
Checking GitLab App ... Finished
Checking GitLab subtasks ... Finished

Possible fixes

None.