Upgrade fails with "functions in index predicate must be marked IMMUTABLE"
Summary
A customer has reported that upgrading from gitlab-ee-13.4.3 to gitlab-ee-13.9.0 failed with migration 20210115220610 ScheduleArtifactExpiryBackfill
failing to create its index with the error:
functions in index predicate must be marked IMMUTABLE
Steps to reproduce
I have not been able to reproduce the issue using stock Postgres server versions 11.10, or 12.4.
Customer's support ticket with some more information can be found here (internal link).
Example Project
This is an upgrade issue on a self-managed Omnibus instance, unrelated to use of a feature.
What is the current bug behavior?
Migration 20210115220610
fails to apply.
What is the expected correct behavior?
Migration 20210115220610
succeeds.
Relevant logs and/or screenshots
gitlab-rake db:migrate RAILS_ENV=production
== 20210115220610 ScheduleArtifactExpiryBackfill: migrating ===================
-- transaction_open?()
-> 0.0000s
-- indexes(:ci_job_artifacts)
-> 0.0072s
-- current_schema()
-> 0.0003s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:ci_job_artifacts, [:id, :created_at], {:where=>"expire_at IS NULL AND date(created_at AT TIME ZONE 'UTC') < '2020-06-22'::date", :name=>"expired_artifacts_temp_index", :algorithm=>:concurrently})
-> 0.0049s
-- execute("SET statement_timeout TO 0")
-> 0.0003s
-- add_index(:ci_job_artifacts, [:id, :created_at], {:where=>"expire_at IS NULL AND date(created_at AT TIME ZONE 'UTC') < '2020-06-22'::date", :name=>"expired_artifacts_temp_index", :algorithm=>:concurrently})
-- execute("RESET ALL")
-> 0.0003s
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:150:in `block in add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:336:in `disable_statement_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:149:in `add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20210115220610_schedule_artifact_expiry_backfill.rb:28:in `up'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
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:150:in `block in add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:336:in `disable_statement_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:149:in `add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20210115220610_schedule_artifact_expiry_backfill.rb:28:in `up'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
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:150:in `block in add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:336:in `disable_statement_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:149:in `add_concurrent_index'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20210115220610_schedule_artifact_expiry_backfill.rb:28:in `up'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Tasks: TOP => db:migrate
Output of checks
Results of GitLab environment info
Expand for output related to GitLab environment info
Not collected yet
Results of GitLab application Check
Expand for output related to the GitLab application check
Not collected yet
Possible fixes
Either the date
or timezone
function applied on the index's predicate appears to be the issue. In some cases could either of these be treated as mutable by Postgres?