Change pages deployments size column to bigint
What does this MR do?
We recently noticed that pages deployments can fail on self-managed instance due to size column being to small: #294221 (closed)
It's not a problem on .com, because we limit pages site size by 2 GB, but self-managed customers can change this limit.
So this MR just changes the column to bigint as described in https://docs.gitlab.com/ee/development/what_requires_downtime.html#changing-column-types
We only have 25571 deployments in production, so I think it's ok to use this simplified version instead of https://docs.gitlab.com/ee/development/what_requires_downtime.html#changing-the-schema-for-large-tables
In %13.6 the problem can be mitigated by turning off the feature flag zip_pages_deployments(on by default), but in %13.7 this feature flag was removed. I can return the feature flag back to life, but I think it's easier to just use Pick into 13.7.
Migrations
== 20201217111448 ChangePagesDeploymentSizeToBigint: migrating ================
-- column_exists?(:pages_deployments, :id)
-> 0.0046s
-- transaction_open?()
-> 0.0000s
-- columns(:pages_deployments)
-> 0.0042s
-- add_column(:pages_deployments, "size_for_type_change", :bigint, {:limit=>4, :precision=>nil, :scale=>nil})
-> 0.0037s
-- transaction_open?()
-> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"pages_deployments\"")
-> 0.0025s
-- exec_query("SELECT \"pages_deployments\".\"id\" FROM \"pages_deployments\" ORDER BY \"pages_deployments\".\"id\" ASC LIMIT 1")
-> 0.0037s
-- exec_query("SELECT \"pages_deployments\".\"id\" FROM \"pages_deployments\" WHERE \"pages_deployments\".\"id\" >= 13 ORDER BY \"pages_deployments\".\"id\" ASC LIMIT 1 OFFSET 1")
-> 0.0035s
-- execute("UPDATE \"pages_deployments\" SET \"size_for_type_change\" = \"pages_deployments\".\"size\" WHERE \"pages_deployments\".\"id\" >= 13")
-> 0.0039s
-- indexes(:pages_deployments)
-> 0.0052s
-- foreign_keys(:pages_deployments)
-> 0.0092s
-- transaction_open?()
-> 0.0000s
-- column_exists?(:pages_deployments, :size)
-> 0.0057s
-- column_exists?(:pages_deployments, "size_for_type_change")
-> 0.0034s
-- current_schema()
-> 0.0008s
-- transaction_open?()
-> 0.0000s
-- current_schema()
-> 0.0009s
-- execute("ALTER TABLE pages_deployments\nADD CONSTRAINT check_5f9132a958\nCHECK ( (size_for_type_change IS NOT NULL) )\nNOT VALID;\n")
-> 0.0016s
-- current_schema()
-> 0.0007s
-- execute("SET statement_timeout TO 0")
-> 0.0005s
-- execute("ALTER TABLE pages_deployments VALIDATE CONSTRAINT check_5f9132a958;")
-> 0.0016s
-- execute("RESET ALL")
-> 0.0003s
-- quote_table_name(:pages_deployments)
-> 0.0000s
-- quote_column_name(:size)
-> 0.0000s
-- quote_column_name("size_for_type_change")
-> 0.0000s
-- execute("CREATE OR REPLACE FUNCTION trigger_448e14608510()\nRETURNS trigger AS\n$BODY$\nBEGIN\n NEW.\"size_for_type_change\" := NEW.\"size\";\n RETURN NEW;\nEND;\n$BODY$\nLANGUAGE 'plpgsql'\nVOLATILE\n")
-> 0.0103s
-- execute("DROP TRIGGER IF EXISTS trigger_448e14608510\nON \"pages_deployments\"\n")
NOTICE: trigger "trigger_448e14608510" for relation "pages_deployments" does not exist, skipping
-> 0.0009s
-- execute("CREATE TRIGGER trigger_448e14608510\nBEFORE INSERT OR UPDATE\nON \"pages_deployments\"\nFOR EACH ROW\nEXECUTE FUNCTION trigger_448e14608510()\n")
-> 0.0019s
== 20201217111448 ChangePagesDeploymentSizeToBigint: migrated (0.1149s) =======
== 20201217112249 ChangePagesDeploymentSizeToBigintCleanup: migrating =========
-- transaction()
-- execute("DROP TRIGGER IF EXISTS trigger_448e14608510 ON pages_deployments")
-> 0.0009s
-- execute("DROP FUNCTION IF EXISTS trigger_448e14608510()")
-> 0.0007s
-- remove_column(:pages_deployments, :size)
-> 0.0014s
-- rename_column(:pages_deployments, "size_for_type_change", :size)
-> 0.0043s
-> 0.0113s
== 20201217112249 ChangePagesDeploymentSizeToBigintCleanup: migrated (0.0115s) Rollback:
./bin/rails db:migrate:down VERSION=20201217112249
./bin/rails db:migrate:down VERSION=20201217111448
== 20201217112249 ChangePagesDeploymentSizeToBigintCleanup: reverting =========
-- column_exists?(:pages_deployments, :id)
-> 0.0147s
-- transaction_open?()
-> 0.0000s
-- columns(:pages_deployments)
-> 0.0051s
-- add_column(:pages_deployments, "tmp_undo_cleanup_column_584828ebef", :integer, {:limit=>4, :precision=>nil, :scale=>nil})
-> 0.0046s
-- transaction_open?()
-> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"pages_deployments\"")
-> 0.0035s
-- exec_query("SELECT \"pages_deployments\".\"id\" FROM \"pages_deployments\" ORDER BY \"pages_deployments\".\"id\" ASC LIMIT 1")
-> 0.0021s
-- exec_query("SELECT \"pages_deployments\".\"id\" FROM \"pages_deployments\" WHERE \"pages_deployments\".\"id\" >= 13 ORDER BY \"pages_deployments\".\"id\" ASC LIMIT 1 OFFSET 1")
-> 0.0012s
-- execute("UPDATE \"pages_deployments\" SET \"tmp_undo_cleanup_column_584828ebef\" = \"pages_deployments\".\"size\" WHERE \"pages_deployments\".\"id\" >= 13")
-> 0.0095s
-- indexes(:pages_deployments)
-> 0.0070s
-- foreign_keys(:pages_deployments)
-> 0.0065s
-- transaction_open?()
-> 0.0000s
-- column_exists?(:pages_deployments, :size)
-> 0.0016s
-- column_exists?(:pages_deployments, "tmp_undo_cleanup_column_584828ebef")
-> 0.0014s
-- current_schema()
-> 0.0003s
-- transaction_open?()
-> 0.0000s
-- current_schema()
-> 0.0027s
-- execute("ALTER TABLE pages_deployments\nADD CONSTRAINT check_2ef9e8d176\nCHECK ( (tmp_undo_cleanup_column_584828ebef IS NOT NULL) )\nNOT VALID;\n")
-> 0.0029s
-- current_schema()
-> 0.0004s
-- execute("SET statement_timeout TO 0")
-> 0.0002s
-- execute("ALTER TABLE pages_deployments VALIDATE CONSTRAINT check_2ef9e8d176;")
-> 0.0012s
-- execute("RESET ALL")
-> 0.0003s
-- transaction()
-- rename_column(:pages_deployments, :size, "size_for_type_change")
-> 0.0040s
-- rename_column(:pages_deployments, "tmp_undo_cleanup_column_584828ebef", :size)
-> 0.0023s
-- quote_table_name(:pages_deployments)
-> 0.0000s
-- quote_column_name(:size)
-> 0.0000s
-- quote_column_name("size_for_type_change")
-> 0.0000s
-- execute("CREATE OR REPLACE FUNCTION trigger_448e14608510()\nRETURNS trigger AS\n$BODY$\nBEGIN\n NEW.\"size_for_type_change\" := NEW.\"size\";\n RETURN NEW;\nEND;\n$BODY$\nLANGUAGE 'plpgsql'\nVOLATILE\n")
-> 0.0206s
-- execute("DROP TRIGGER IF EXISTS trigger_448e14608510\nON \"pages_deployments\"\n")
NOTICE: trigger "trigger_448e14608510" for relation "pages_deployments" does not exist, skipping
-> 0.0006s
-- execute("CREATE TRIGGER trigger_448e14608510\nBEFORE INSERT OR UPDATE\nON \"pages_deployments\"\nFOR EACH ROW\nEXECUTE FUNCTION trigger_448e14608510()\n")
-> 0.0014s
-> 0.0317s
== 20201217112249 ChangePagesDeploymentSizeToBigintCleanup: reverted (0.1462s)
== 20201217111448 ChangePagesDeploymentSizeToBigint: reverting ================
-- execute("DROP TRIGGER IF EXISTS trigger_448e14608510 ON pages_deployments")
-> 0.0052s
-- execute("DROP FUNCTION IF EXISTS trigger_448e14608510()")
-> 0.0013s
-- remove_column(:pages_deployments, "size_for_type_change")
-> 0.0022s
== 20201217111448 ChangePagesDeploymentSizeToBigint: reverted (0.0109s) =======Does this MR meet the acceptance criteria?
Conformity
- Changelog entry
- 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 #294221 (closed)