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
-
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
-
Using
restrict_gitlab_migration gitlab_schema: :gitlab_main
does not work becauseRestrictAllowedSchemas
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 inif 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