Skip to content

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?