Add migration to update subscription plans
What does this MR do?
Relates to #321364 (closed)
This MR updates hosted_plan_id for the subscriptions (GitlabSubscription records) that were created after EoA: these subscriptions were still pointing to the deprecated gold and silver plans.
Records to be updated in Production
As of March 17 2021
we have:
-
660subscriptions tied to the silver plan to be updated with the premium plan -
8487subscriptions tied to the gold plan to be updated with the ultimate plan.
As of May 4 2021
we have:
-
938subscriptions tied to the silver plan to be updated with the premium plan -
117subscriptions tied to the gold plan to be updated with the ultimate plan.
Up/Down migration
etienne@dell ~/src/gdk/gitlab(321364-update-post-eoa-subscriptions ✗) rails db:migrate:up VERSION=20210303121224
== 20210303121224 UpdateGitlabSubscriptionsStartAtPostEoa: migrating ==========
-- transaction_open?()
-> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"gitlab_subscriptions\" WHERE \"gitlab_subscriptions\".\"start_date\" >= '2021-01-26' AND \"gitlab_subscriptions\".\"hosted_plan_id\" = 2")
-> 0.0010s
-- exec_query("SELECT \"gitlab_subscriptions\".\"id\" FROM \"gitlab_subscriptions\" WHERE \"gitlab_subscriptions\".\"start_date\" >= '2021-01-26' AND \"gitlab_subscriptions\".\"hosted_plan_id\" = 2 ORDER BY \"gitlab_subscriptions\".\"id\" ASC LIMIT 1")
-> 0.0009s
-- exec_query("SELECT \"gitlab_subscriptions\".\"id\" FROM \"gitlab_subscriptions\" WHERE \"gitlab_subscriptions\".\"id\" >= 54 AND \"gitlab_subscriptions\".\"start_date\" >= '2021-01-26' AND \"gitlab_subscriptions\".\"hosted_plan_id\" = 2 ORDER BY \"gitlab_subscriptions\".\"id\" ASC LIMIT 1 OFFSET 1")
-> 0.0008s
-- execute("UPDATE \"gitlab_subscriptions\" SET \"hosted_plan_id\" = 10 WHERE \"gitlab_subscriptions\".\"id\" >= 54 AND \"gitlab_subscriptions\".\"start_date\" >= '2021-01-26' AND \"gitlab_subscriptions\".\"hosted_plan_id\" = 2")
-> 0.0045s
-- transaction_open?()
-> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"gitlab_subscriptions\" WHERE \"gitlab_subscriptions\".\"start_date\" >= '2021-01-26' AND \"gitlab_subscriptions\".\"hosted_plan_id\" = 3")
-> 0.0007s
== 20210303121224 UpdateGitlabSubscriptionsStartAtPostEoa: migrated (0.0207s) =
etienne@dell ~/src/gdk/gitlab(321364-update-post-eoa-subscriptions ✗) rails db:migrate:down VERSION=20210303121224
== 20210303121224 UpdateGitlabSubscriptionsStartAtPostEoa: reverting ==========
-- transaction_open?()
-> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"gitlab_subscriptions\" WHERE \"gitlab_subscriptions\".\"start_date\" >= '2021-01-26' AND \"gitlab_subscriptions\".\"hosted_plan_id\" = 10")
-> 0.0011s
-- exec_query("SELECT \"gitlab_subscriptions\".\"id\" FROM \"gitlab_subscriptions\" WHERE \"gitlab_subscriptions\".\"start_date\" >= '2021-01-26' AND \"gitlab_subscriptions\".\"hosted_plan_id\" = 10 ORDER BY \"gitlab_subscriptions\".\"id\" ASC LIMIT 1")
-> 0.0009s
-- exec_query("SELECT \"gitlab_subscriptions\".\"id\" FROM \"gitlab_subscriptions\" WHERE \"gitlab_subscriptions\".\"id\" >= 54 AND \"gitlab_subscriptions\".\"start_date\" >= '2021-01-26' AND \"gitlab_subscriptions\".\"hosted_plan_id\" = 10 ORDER BY \"gitlab_subscriptions\".\"id\" ASC LIMIT 1 OFFSET 1")
-> 0.0007s
-- execute("UPDATE \"gitlab_subscriptions\" SET \"hosted_plan_id\" = 2 WHERE \"gitlab_subscriptions\".\"id\" >= 54 AND \"gitlab_subscriptions\".\"start_date\" >= '2021-01-26' AND \"gitlab_subscriptions\".\"hosted_plan_id\" = 10")
-> 0.0306s
-- transaction_open?()
-> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"gitlab_subscriptions\" WHERE \"gitlab_subscriptions\".\"start_date\" >= '2021-01-26' AND \"gitlab_subscriptions\".\"hosted_plan_id\" = 11")
-> 0.0007s
== 20210303121224 UpdateGitlabSubscriptionsStartAtPostEoa: reverted (0.0480s) =SQL plans
For the following query:
UPDATE "gitlab_subscriptions" SET "hosted_plan_id" = 10 WHERE "gitlab_subscriptions"."id" >= 54 AND "gitlab_subscriptions"."start_date" >= '2021-01-26' AND "gitlab_subscriptions"."hosted_plan_id" = 2"we have:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1)
Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
Sort Method: quicksort Memory: 43kB
-> Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1)
Hash Cond: (p.pronamespace = n.oid)
-> Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2402 loops=1)
Filter: pg_function_is_visible(oid)
-> Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1)
Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))Checking new subscriptions
This MR was merged on March 31st. Its goal was to have CustomersDot to send premium and ultimate plan code to GitLab.com when creating new subscriptions, instead of the deprecated gold and silver.
gitlabhq_production=> select start_date, count(*) as new_gold_silver from gitlab_subscriptions where hosted_plan_id IN (3, 4) AND start_date > '2021-03-20' group by start_date;
start_date | new_gold_silver
------------+-----------------
2021-03-21 | 185
2021-03-22 | 309
2021-03-23 | 369
2021-03-24 | 318
2021-03-25 | 327
2021-03-26 | 323
2021-03-27 | 188
2021-03-28 | 202
2021-03-29 | 319
2021-03-30 | 331
2021-03-31 | 258
2021-04-01 | 12
2021-04-02 | 4
2021-04-03 | 1
2021-04-04 | 1
2021-04-06 | 1
2021-09-24 | 1
gitlabhq_production=> select start_date, count(*) as new_premium_ultimate from gitlab_subscriptions where hosted_plan_id IN (100, 101) AND created_at > '2021-03-20' group by start_date;
start_date | new_premium_ultimate
------------+----------------------
2021-03-21 | 1
2021-03-22 | 3
2021-03-23 | 1
2021-03-25 | 3
2021-03-26 | 2
2021-03-29 | 3
2021-03-30 | 3
2021-03-31 | 35
2021-04-01 | 354
2021-04-02 | 217
2021-04-03 | 190
2021-04-04 | 155
2021-04-05 | 265
2021-04-06 | 172However, since March 31st, some gitlab_subscriptions with a deprecated plan were still showing a start_date after March 31st. I looked into them:
gitlabhq_production=> select created_at from gitlab_subscriptions where hosted_plan_id IN (3, 4) AND start_date > '2021-03-31' order by created_at asc;
created_at
-------------------------------
2018-12-12 23:15:40.969007+00
2018-12-13 06:33:33.275284+00
2018-12-13 06:51:50.624384+00
2018-12-13 07:43:36.401869+00
2018-12-14 14:53:29.393042+00
2019-02-13 15:06:43.552892+00
2019-02-15 10:28:52.739785+00
2019-07-19 14:10:23.466374+00
2019-08-07 20:12:54.322189+00
2019-09-06 19:32:36.666739+00
2019-10-02 15:25:45.209575+00
2019-12-10 09:01:59.910018+00
2020-01-16 14:36:53.533923+00
2020-02-25 16:33:17.24921+00
2020-03-03 11:14:43.224985+00
2020-03-10 10:14:15.924393+00
2020-04-01 18:22:40.085955+00
2020-06-02 15:40:44.066303+00
2021-01-27 21:11:37.667505+00
2021-02-09 12:20:30.19194+00They were all created prior to March 31st: these are old subscriptions to which start_date was recently updated. Why? Was it part of an auto-renewal process? I'm not sure yet, I asked for some help over at #support_licensing-subscription
It also means that no new subscription were created with a deprecated plan name
Checking new subscriptions (on May 4th)
Re-running that same query I ran a few weeks ago (see section above):
gitlabhq_production=> select start_date, count(*) as new_gold_silver from gitlab_subscriptions
where hosted_plan_id IN (3, 4) AND start_date > '2021-03-20'
group by start_date order by start_date asc;
start_date | new_gold_silver
------------+-----------------
2021-03-21 | 5
2021-03-22 | 23
2021-03-23 | 21
2021-03-24 | 17
2021-03-25 | 19
2021-03-26 | 27
2021-03-27 | 12
2021-03-28 | 8
2021-03-29 | 11
2021-03-30 | 16
2021-03-31 | 16
2021-04-01 | 11
2021-04-02 | 3
2021-04-03 | 1
2021-04-04 | 1
2021-04-06 | 1
2021-04-07 | 2
2021-04-08 | 1
2021-04-09 | 1
2021-04-10 | 3
2021-04-11 | 1
2021-04-13 | 3
2021-04-14 | 1
2021-04-15 | 2
2021-04-16 | 1
2021-04-17 | 3
2021-04-18 | 2
2021-04-19 | 1
2021-04-20 | 3
2021-04-21 | 2
2021-04-22 | 2
2021-04-23 | 6
2021-04-24 | 4
2021-04-25 | 2
2021-04-26 | 6
2021-04-27 | 6
2021-04-28 | 2
2021-04-29 | 4
2021-04-30 | 7
2021-05-01 | 2
2021-05-02 | 1
2021-05-03 | 3
2021-05-04 | 2
2021-09-24 | 1Checking when the latest remaining subscription with either Gold or Silver plan was created, after we sync'ed up plan names between CustomersDot and GitLab.
gitlabhq_production=> select created_at from gitlab_subscriptions
where hosted_plan_id IN (3, 4) order by created_at desc limit 2;
created_at
-------------------------------
2021-04-05 20:31:09.828551+00
2021-03-30 21:52:31.038445+00
Does this MR meet the acceptance criteria?
Conformity
-
📋 Does this MR need a changelog?-
I have included a changelog entry. - [-] I have not included a changelog entry because _____.
-
- [-] Documentation (if required)
-
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. - [-] Tested in all supported browsers
- [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
- [-] Label as security and @ mention
@gitlab-com/gl-security/appsec - [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
- [-] Security reports checked/validated by a reviewer from the AppSec team
Related to #321364 (closed)
Edited by Mayra Cabrera