Skip to content

Fix OrphanedInviteTokensCleanup migration

Stan Hu requested to merge sh-fix-orphaned-tokens-migration into master

Some database schemas have members.created_at set as timestamp with time zone, while on GitLab.com and dev we have timestamp without time zone. If the column has a time zone, the OrphanedInviteTokensCleanup migration fails with the following error:

PG::InvalidObjectDefinition: ERROR: functions in index predicate must be marked IMMUTABLE

As described in https://stackoverflow.com/a/58848792, if the time zone depends on the server, the result might change if somebody changes the time zone. We need to lock the time zone if the timezone depends on the server.

We need to make sure the comparison between created_at and invite_accepted_at are done using the same types or PostgreSQL will reject the query due to immutability concerns. Calling TIMEZONE('UTC', column) on a timestamp without time zone converts it to timestamp with time zone type. Calling the same function on a timestamp with time zone converts it to a type of timestamp without time zone. Therefore, we should check the type before we do this conversion.

There is an epic that deals with reconciling these datetime with timezone differences: &2473

Relates to #339091 (closed)

Testing locally

  1. Run gdk psql and \c gitlabhq_test.
  2. Change the members column type:
gitlabhq_test=# alter table members alter created_at type timestamp with time zone;
ALTER TABLE
  1. Run RAILS_ENV=test bundle exec rake db:migrate:redo VERSION=20210809123658

Migration output

$ RAILS_ENV=test bundle exec rake db:migrate:redo VERSION=20210809123658
== 20210809123658 OrphanedInviteTokensCleanup: migrating ======================
-- transaction_open?()
   -> 0.0000s
-- index_exists?("members", :id, {:where=>"invite_token IS NOT NULL and invite_accepted_at IS NOT NULL and TIMEZONE('UTC', invite_accepted_at) < TIMEZONE('UTC', created_at)", :name=>"tmp_idx_orphaned_invite_tokens", :algorithm=>:concurrently})
   -> 0.0085s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- add_index("members", :id, {:where=>"invite_token IS NOT NULL and invite_accepted_at IS NOT NULL and TIMEZONE('UTC', invite_accepted_at) < TIMEZONE('UTC', created_at)", :name=>"tmp_idx_orphaned_invite_tokens", :algorithm=>:concurrently})
   -> 0.0070s
-- execute("RESET statement_timeout")
   -> 0.0008s
-- transaction_open?()
   -> 0.0000s
-- indexes("members")
   -> 0.0074s
-- remove_index("members", {:algorithm=>:concurrently, :name=>"tmp_idx_orphaned_invite_tokens"})
   -> 0.0033s
== 20210809123658 OrphanedInviteTokensCleanup: migrated (0.0470s) =============
Edited by Stan Hu

Merge request reports

Loading