Change pages deployments size column to bigint (!50262) · Merge requests · GitLab.org / GitLab

Vladimir Shushlin requested to merge 294221-gitlab-pages-deployments-over-the-max-int-size-in-bytes-fail-to-upload into master Dec 17, 2020

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

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)

Edited Dec 23, 2020 by Vladimir Shushlin

Change pages deployments size column to bigint