QueryAnalyzer: TRUNCATE is considered DDL

We recently discovered in !106894 (comment 1216992707) that TRUNCATE is considered a DDL statement, not DML so migrations with such a statement is running in all databases.

Example

[1] pry(main)> parsed = PgQuery.parse 'TRUNCATE sbom_occurrences'
=> #<PgQuery::ParserResult:0x000000012dfaf158
 @aliases=nil,
 @cte_names=nil,
 @functions=nil,
 @query="TRUNCATE sbom_occurrences",
 @tables=nil,
 @tree=
  <PgQuery::ParseResult: version: 130008, stmts: [<PgQuery::RawStmt: stmt: <PgQuery::Node: truncate_stmt: <PgQuery::TruncateStmt: relations: [<PgQuery::Node: range_var: <PgQuery::RangeVar: catalogname: "", schemaname: "", relname: "sbom_occurrences", inh: true, relpersistence: "p", location: 9>>], restart_seqs: false, behavior: :DROP_RESTRICT>>, stmt_location: 0, stmt_len: 0>]>,
 @warnings=[]>
[2] pry(main)> parsed.dml_tables
=> []
[3] pry(main)> parsed.ddl_tables
=> ["sbom_occurrences"]

Problem

  1. On GitLab.com, lock writes is enabled. So this migration will run on CI and hit:

    StandardError: An error has occurred, this and all later migrations canceled:
PG::SREModifyingSqlDataNotPermitted: ERROR:  Table: "sbom_occurrences" is write protected within this Gitlab database.
HINT:  Make sure you are using the right database connection

  2. Using restrict_gitlab_migration gitlab_schema: :gitlab_main does not work because RestrictAllowedSchemas does not allow mixing of DML mode and DDL statements

Workaround

Use something like:

    execute('TRUNCATE sbom_occurrences') if Gitlab::Database.gitlab_schemas_for_connection(connection).include?(:gitlab_main)

Which means this migration will only execute for the main database.

Solution (discussed in the comments down)

  • We create a migration helper called truncate_table. That basically encapsulates the logic of truncating the right table on the right database using the logic that we have now in if Gitlab::Database.gitlab_schemas_for_connection(connection).include?(:gitlab_main). I think having the helper is a good idea, because if we want to change the logic of truncating a table, we can change the helper instead of fixing tens of database migrations.

  • We document this in https://docs.gitlab.com/ee/development/migration_style_guide.html

/cc @bwill @OmarQunsulGitlab @DylanGriffith @ayufan

Edited by Thong Kuah