Add data migration to flip draft status column false->true for all open wip/draft MRs
requested to merge 330276-add-data-migration-to-flip-draft-status-column-false-true-for-all-open-wip-draft-mrs into master
What does this MR do?
Adds a data migration to backfill draft
for all open draft MRs.
Database
Up
== 20210526222715 BackfillDraftStatusOnMergeRequests: migrating ===============
-- indexes(:merge_requests)
-> 0.0287s
-- current_schema()
-> 0.0003s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:merge_requests, [:state_id, :draft, :title], {:where=>"state_id = 1 AND draft = false AND((title)::text ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'::text)", :name=>"tmp_index_merge_requests_draft_and_status", :algorithm=>:concurrently})
-> 0.0164s
-- execute("SET statement_timeout TO 0")
-> 0.0009s
-- add_index(:merge_requests, [:state_id, :draft, :title], {:where=>"state_id = 1 AND draft = false AND((title)::text ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'::text)", :name=>"tmp_index_merge_requests_draft_and_status", :algorithm=>:concurrently})
-> 0.0109s
-- execute("RESET ALL")
-> 0.0013s
-- transaction_open?()
-> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"merge_requests\" WHERE \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"draft\" = FALSE AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'")
-> 0.0144s
-- exec_query("SELECT \"merge_requests\".\"id\" FROM \"merge_requests\" WHERE \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP' ORDER BY \"merge_requests\".\"id\" ASC LIMIT 1")
-> 0.0028s
-- exec_query("SELECT \"merge_requests\".\"id\" FROM \"merge_requests\" WHERE \"merge_requests\".\"id\" >= 99 AND \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP' ORDER BY \"merge_requests\".\"id\" ASC LIMIT 1 OFFSET 1")
-> 0.0029s
-- execute("UPDATE \"merge_requests\" SET \"draft\" = TRUE WHERE \"merge_requests\".\"id\" >= 99 AND \"merge_requests\".\"id\" < 212 AND \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'")
-> 0.0049s
-- exec_query("SELECT \"merge_requests\".\"id\" FROM \"merge_requests\" WHERE \"merge_requests\".\"id\" >= 212 AND \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP' ORDER BY \"merge_requests\".\"id\" ASC LIMIT 1 OFFSET 1")
-> 0.0019s
-- execute("UPDATE \"merge_requests\" SET \"draft\" = TRUE WHERE \"merge_requests\".\"id\" >= 212 AND \"merge_requests\".\"id\" < 214 AND \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'")
-> 0.0016s
-- exec_query("SELECT \"merge_requests\".\"id\" FROM \"merge_requests\" WHERE \"merge_requests\".\"id\" >= 214 AND \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP' ORDER BY \"merge_requests\".\"id\" ASC LIMIT 1 OFFSET 1")
-> 0.0015s
-- execute("UPDATE \"merge_requests\" SET \"draft\" = TRUE WHERE \"merge_requests\".\"id\" >= 214 AND \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'")
-> 0.0021s
-- indexes(:merge_requests)
-> 0.0149s
-- transaction_open?()
-> 0.0000s
-- indexes(:merge_requests)
-> 0.0158s
-- remove_index(:merge_requests, {:algorithm=>:concurrently, :name=>"tmp_index_merge_requests_draft_and_status"})
-> 0.0047s
== 20210526222715 BackfillDraftStatusOnMergeRequests: migrated (0.1165s) ======
Down
== 20210526222715 BackfillDraftStatusOnMergeRequests: reverting ===============
-- indexes(:merge_requests)
-> 0.0197s
-- current_schema()
-> 0.0002s
== 20210526222715 BackfillDraftStatusOnMergeRequests: reverted (0.0233s) ======
Temp Index
exec CREATE INDEX merge_request_draft_temp_index ON merge_requests USING btree (id) WHERE state_id = 1 AND draft = FALSE AND ((title)::text ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'::text);
The query has been executed. Duration: 52.945 min
\di+ merge_request_draft_temp_index
Session: 4317
Command output:
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+--------------------------------+-------+--------------+----------------+---------+-------------
public | merge_request_draft_temp_index | index | joe_acroitor | merge_requests | 5784 kB |
(1 row)
- With the index:
explain SELECT id FROM "merge_requests" WHERE "merge_requests"."state_id" = 1 AND "merge_requests"."draft" = FALSE AND "merge_requests"."title" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'
Index Only Scan using merge_request_draft_temp_index on public.merge_requests (cost=0.42..1315.74 rows=58377 width=4) (actual time=0.084..146.856 rows=262468 loops=1)
Heap Fetches: 6684
Buffers: shared hit=226833
I/O Timings: read=0.000 write=0.000
Time: 184.845 ms
- planning: 18.312 ms
- execution: 166.533 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 226833 (~1.70 GiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Related to #330276 (closed)
Edited by Kerri Miller