Fix OrphanedInviteTokensCleanup migration
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
- Run
gdk psql
and\c gitlabhq_test
. - Change the
members
column type:
gitlabhq_test=# alter table members alter created_at type timestamp with time zone;
ALTER TABLE
- 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) =============