Unable to run pipelines manually

Hi all.

We have a self hosted GitLab Community edition. After upgrading from version 16.8.1 to 16.9.0 nobody is able to run a pipeline manually. This is the message that GitLab returns:

Then I used this command “sudo gitlab-ctl tail” and found the following two errors that I think are related to the problem:

==> /var/log/gitlab/postgresql/current <==
2024-02-20_06:34:46.13227 ERROR: duplicate key value violates unique constraint “ci_pipeline_variables_pkey”
2024-02-20_06:34:46.13229 DETAIL: Key (id, partition_id)=(38, 100) already exists.
2024-02-20_06:34:46.13230 STATEMENT: /application:web,correlation_id:01HQ2KJNT9RZ8B6MGR4016VG44,endpoint_id:Projects::PipelinesController#create,db_config_name:ci/ INSERT INTO “p_ci_pipeline_variables” (“key”, “value”, “encrypted_value”, “encrypted_value_salt”, “encrypted_value_iv”, “partition_id”, “pipeline_id”) VALUES (‘ico’, NULL, '(I deleted the value here)==
2024-02-20_06:34:46.13231 ', '(I deleted the value here)==
2024-02-20_06:34:46.13232 ', '(I deleted the value here)==
2024-02-20_06:34:46.13232 ', 100, 25474) RETURNING “id”

==> /var/log/gitlab/gitlab-rails/production.log <==

ActiveRecord::RecordNotUnique (PG::UniqueViolation: ERROR: duplicate key value violates unique constraint “ci_pipeline_variables_pkey”
DETAIL: Key (id, partition_id)=(38, 100) already exists.
):

lib/gitlab/database/load_balancing/connection_proxy.rb:127:in public_send' lib/gitlab/database/load_balancing/connection_proxy.rb:127:in block in write_using_load_balancer’
lib/gitlab/database/load_balancing/load_balancer.rb:141:in block in read_write' lib/gitlab/database/load_balancing/load_balancer.rb:228:in retry_with_backoff’
lib/gitlab/database/load_balancing/load_balancer.rb:130:in read_write' lib/gitlab/database/load_balancing/connection_proxy.rb:126:in write_using_load_balancer’
lib/gitlab/database/load_balancing/connection_proxy.rb:61:in block (2 levels) in <class:ConnectionProxy>' lib/gitlab/database/load_balancing/connection_proxy.rb:127:in public_send’
lib/gitlab/database/load_balancing/connection_proxy.rb:127:in block in write_using_load_balancer' lib/gitlab/database/load_balancing/load_balancer.rb:141:in block in read_write’
lib/gitlab/database/load_balancing/load_balancer.rb:228:in retry_with_backoff' lib/gitlab/database/load_balancing/load_balancer.rb:130:in read_write’
lib/gitlab/database/load_balancing/connection_proxy.rb:126:in write_using_load_balancer' lib/gitlab/database/load_balancing/connection_proxy.rb:78:in transaction’
lib/gitlab/database/load_balancing/connection_proxy.rb:127:in public_send' lib/gitlab/database/load_balancing/connection_proxy.rb:127:in block in write_using_load_balancer’
lib/gitlab/database/load_balancing/load_balancer.rb:141:in block in read_write' lib/gitlab/database/load_balancing/load_balancer.rb:228:in retry_with_backoff’
lib/gitlab/database/load_balancing/load_balancer.rb:130:in read_write' lib/gitlab/database/load_balancing/connection_proxy.rb:126:in write_using_load_balancer’
lib/gitlab/database/load_balancing/connection_proxy.rb:78:in transaction' lib/gitlab/ci/pipeline/chain/create.rb:16:in block (4 levels) in perform!’
app/models/concerns/cross_database_modification.rb:92:in block in transaction' lib/gitlab/database/load_balancing/connection_proxy.rb:127:in public_send’
lib/gitlab/database/load_balancing/connection_proxy.rb:127:in block in write_using_load_balancer' lib/gitlab/database/load_balancing/load_balancer.rb:141:in block in read_write’
lib/gitlab/database/load_balancing/load_balancer.rb:228:in retry_with_backoff' lib/gitlab/database/load_balancing/load_balancer.rb:130:in read_write’
lib/gitlab/database/load_balancing/connection_proxy.rb:126:in write_using_load_balancer' lib/gitlab/database/load_balancing/connection_proxy.rb:78:in transaction’
lib/gitlab/database.rb:359:in block in transaction' lib/gitlab/database.rb:358:in transaction’
app/models/concerns/cross_database_modification.rb:83:in transaction' lib/gitlab/ci/pipeline/chain/create.rb:15:in block (3 levels) in perform!’
app/models/concerns/ci/bulk_insertable_tags.rb:13:in with_bulk_insert_tags' lib/gitlab/ci/pipeline/chain/create.rb:14:in block (2 levels) in perform!’
app/models/concerns/bulk_insertable_associations.rb:54:in with_bulk_insert' lib/gitlab/ci/pipeline/chain/create.rb:13:in block in perform!’
lib/gitlab/ci/pipeline/logger.rb:27:in instrument' lib/gitlab/ci/pipeline/logger.rb:43:in instrument_once_with_sql’
lib/gitlab/ci/pipeline/chain/create.rb:12:in perform!' lib/gitlab/ci/pipeline/chain/sequence.rb:20:in block in build!’
lib/gitlab/ci/pipeline/chain/sequence.rb:16:in each' lib/gitlab/ci/pipeline/chain/sequence.rb:16:in build!’
app/services/ci/create_pipeline_service.rb:93:in execute' app/controllers/projects/pipelines_controller.rb:85:in create’
ee/lib/gitlab/ip_address_state.rb:10:in with' ee/app/controllers/ee/application_controller.rb:45:in set_current_ip_address’
app/controllers/application_controller.rb:468:in set_current_admin' lib/gitlab/session.rb:11:in with_session’
app/controllers/application_controller.rb:459:in set_session_storage' lib/gitlab/i18n.rb:114:in with_locale’
lib/gitlab/i18n.rb:120:in with_user_locale' app/controllers/application_controller.rb:450:in set_locale’
app/controllers/application_controller.rb:443:in set_current_context' lib/gitlab/metrics/elasticsearch_rack_middleware.rb:16:in call’
lib/gitlab/middleware/memory_report.rb:13:in call' lib/gitlab/middleware/speedscope.rb:13:in call’
lib/gitlab/database/load_balancing/rack_middleware.rb:23:in call' lib/gitlab/middleware/rails_queue_duration.rb:33:in call’
lib/gitlab/etag_caching/middleware.rb:21:in call' lib/gitlab/metrics/rack_middleware.rb:16:in block in call’
lib/gitlab/metrics/web_transaction.rb:46:in run' lib/gitlab/metrics/rack_middleware.rb:16:in call’
lib/gitlab/middleware/go.rb:20:in call' lib/gitlab/middleware/query_analyzer.rb:11:in block in call’
lib/gitlab/database/query_analyzer.rb:40:in within' lib/gitlab/middleware/query_analyzer.rb:11:in call’
lib/gitlab/middleware/multipart.rb:173:in call' lib/gitlab/middleware/read_only/controller.rb:50:in call’
lib/gitlab/middleware/read_only.rb:18:in call' lib/gitlab/middleware/unauthenticated_session_expiry.rb:18:in call’
lib/gitlab/middleware/same_site_cookies.rb:27:in call' lib/gitlab/middleware/path_traversal_check.rb:35:in call’
lib/gitlab/middleware/handle_malformed_strings.rb:21:in call' lib/gitlab/middleware/basic_health_check.rb:25:in call’
lib/gitlab/middleware/handle_ip_spoof_attack_error.rb:25:in call' lib/gitlab/middleware/request_context.rb:15:in call’
lib/gitlab/middleware/webhook_recursion_detection.rb:15:in call' config/initializers/fix_local_cache_middleware.rb:11:in call’
lib/gitlab/middleware/compressed_json.rb:44:in call' lib/gitlab/middleware/rack_multipart_tempfile_factory.rb:19:in call’
lib/gitlab/middleware/sidekiq_web_static.rb:20:in call' lib/gitlab/metrics/requests_rack_middleware.rb:79:in call’
lib/gitlab/middleware/release_env.rb:13:in `call’

If I remove the variable the pipeline starts without a problem:

On another self hosted GitLab Community which also was upgraded from version 16.8.1 to 16.9.0 we can start pipelines manually with variables without any problem. Does anyone know what might be the cause of this?

@hristo.dobrevski in one of the migrations included in this release we expected to have only one sequence per table, but for some still unknown reasons(investigating) there were two and the migration picked the wrong sequence.

To check if this is the problem you are facing, please execute \df+ assign_p_ci_pipeline_variables_id_value in a psql session. The command should return something like this:

gitlabhq_development_ci=# \df+ assign_p_ci_pipeline_variables_id_value
                                                                                                                                List of functions
 Schema |                  Name                   | Result data type | Argument data types | Type | Volatility | Parallel | Owner  | Security | Access privileges | Language |                                     Source code                                     | Description
--------+-----------------------------------------+------------------+---------------------+------+------------+----------+--------+----------+-------------------+----------+-------------------------------------------------------------------------------------+-------------
 public | assign_p_ci_pipeline_variables_id_value | trigger          |                     | func | volatile   | unsafe   | marius | invoker  |                   | plpgsql  |                                                                                    +|
        |                                         |                  |                     |      |            |          |        |          |                   |          | BEGIN                                                                              +|
        |                                         |                  |                     |      |            |          |        |          |                   |          | IF NEW."id" IS NOT NULL THEN                                                       +|
        |                                         |                  |                     |      |            |          |        |          |                   |          |   RAISE WARNING 'Manually assigning ids is not allowed, the value will be ignored';+|
        |                                         |                  |                     |      |            |          |        |          |                   |          | END IF;                                                                            +|
        |                                         |                  |                     |      |            |          |        |          |                   |          | NEW."id" := nextval('ci_pipeline_variables_id_seq'::regclass);                     +|
        |                                         |                  |                     |      |            |          |        |          |                   |          | RETURN NEW;                                                                        +|
        |                                         |                  |                     |      |            |          |        |          |                   |          |                                                                                    +|
        |                                         |                  |                     |      |            |          |        |          |                   |          | END                                                                                +|
        |                                         |                  |                     |      |            |          |        |          |                   |          |                                                                                     |
(1 row)

If that function is using any other sequence than ci_pipeline_variables_id_seq you need to recreate it using:

CREATE OR REPLACE FUNCTION assign_p_ci_pipeline_variables_id_value() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
IF NEW."id" IS NOT NULL THEN
  RAISE WARNING 'Manually assigning ids is not allowed, the value will be ignored';
END IF;
NEW."id" := nextval('ci_pipeline_variables_id_seq'::regclass);
RETURN NEW;

END
$$;
1 Like

@mbobin This GitLab instance is a production environment. If I understand correctly, the df+ command will only list the function with more details so I assume it should be safe to execute it or I am wrong?

@hristo.dobrevski yes, \df+ is a read only function: PostgreSQL: Documentation: 16: psql

1 Like

It is using the sequence ci_builds_runner_session_id_seq instead of ci_pipeline_variables_id_seq.

The code that recreates the function must also be executed in a psql session?

yes, you need to do it in a psql session.

1 Like

The issue seems to be resolved. Thank you very much for the help!

I can confirm seems to resolve.
We had same issue after upgrading from on-prem docker instance 16.8.1 → 16.9.1.
Working fine after \df+ … stuff :wink: