ERROR: functions in index predicate must be marked IMMUTABLE while running 14.4 PostgreSQL migrations
When running migrations for 14.4, I encounter the following error:
System Info:
------------
chef_version=15.17.4
platform=ubuntu
platform_version=20.04
ruby=ruby 2.7.4p191 (2021-07-07 revision a21a3b7d23) [x86_64-linux]
program_name=/opt/gitlab/embedded/bin/chef-client
executable=/opt/gitlab/embedded/bin/chef-client
Recipe: gitlab::gitlab-rails
* execute[clear the gitlab-rails cache] action run
- execute /opt/gitlab/bin/gitlab-rake cache:clear
Running handlers:
There was an error running gitlab-ctl reconfigure:
rails_migration[gitlab-rails] (gitlab::database_migrations line 51) had an error: Mixlib::ShellOut::ShellCommandFailed: bash[migrate gitlab-rails database] (/opt/gitlab/embedded/cookbooks/cache/cookbooks/gitlab/re
sources/rails_migration.rb line 16) had an error: Mixlib::ShellOut::ShellCommandFailed: Expected process to exit with [0], but received '1'
---- Begin output of "bash" "/tmp/chef-script20211024-34-zorbw" ----
STDOUT: rake aborted!
StandardError: An error has occurred, all later migrations canceled:
PG::InvalidObjectDefinition: ERROR: functions in index predicate must be marked IMMUTABLE
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:155:in `block in add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:359:in `disable_statement_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:154:in `add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20211004110500_add_temporary_index_to_issue_metrics.rb:9:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:31:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:61:in `block (3 levels) in '
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `'
Caused by:
ActiveRecord::StatementInvalid: PG::InvalidObjectDefinition: ERROR: functions in index predicate must be marked IMMUTABLE
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:155:in `block in add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:359:in `disable_statement_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:154:in `add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20211004110500_add_temporary_index_to_issue_metrics.rb:9:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:31:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:61:in `block (3 levels) in '
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `'
Caused by:
PG::InvalidObjectDefinition: ERROR: functions in index predicate must be marked IMMUTABLE
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:155:in `block in add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:359:in `disable_statement_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:154:in `add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20211004110500_add_temporary_index_to_issue_metrics.rb:9:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:31:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:61:in `block (3 levels) in '
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
== 20211004110500 AddTemporaryIndexToIssueMetrics: migrating ==================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:issue_metrics, :issue_id, {:where=>"EXTRACT(YEAR FROM first_mentioned_in_commit_at) > 2019", :name=>"index_issue_metrics_first_mentioned_in_commit", :algorithm=>:concurrently})
-> 0.0055s
-- execute("SET statement_timeout TO 0")
-> 0.0002s
-- add_index(:issue_metrics, :issue_id, {:where=>"EXTRACT(YEAR FROM first_mentioned_in_commit_at) > 2019", :name=>"index_issue_metrics_first_mentioned_in_commit", :algorithm=>:concurrently})
-- execute("RESET statement_timeout")
-> 0.0002s
STDERR:
---- End output of "bash" "/tmp/chef-script20211024-34-zorbw" ----
Ran "bash" "/tmp/chef-script20211024-34-zorbw" returned 1
This looks very similar to #339091 (closed)
issue_metrics looks like this:
gitlabhq_production-# \d issue_metrics
Table "public.issue_metrics"
Column | Type | Collation | Nullable | Default
------------------------------------+--------------------------+-----------+----------+-------------------------------------------
id | integer | | not null | nextval('issue_metrics_id_seq'::regclass)
issue_id | integer | | not null |
first_mentioned_in_commit_at | timestamp with time zone | | |
first_associated_with_milestone_at | timestamp with time zone | | |
first_added_to_board_at | timestamp with time zone | | |
created_at | timestamp with time zone | | |
updated_at | timestamp with time zone | | |
Indexes:
"issue_metrics_pkey" PRIMARY KEY, btree (id)
"index_unique_issue_metrics_issue_id" UNIQUE, btree (issue_id)
"index_issue_metrics_on_issue_id_and_timestamps" btree (issue_id, first_mentioned_in_commit_at, first_associated_with_milestone_at, first_added_to_board_at)
"issue_metrics_issue_id_idx" btree (issue_id)
Foreign-key constraints:
"fk_rails_4bb543d85d" FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED