Skip to content

Add a helper to truncate tables in migrations

Thong Kuah requested to merge truncate_table_helper into master

What does this MR do and why?

As we cannot truncate tables not in schema for the connection, due to lock write triggers. We have been doing this for some migrations now, so we create this helper for ease of use

Add tests

Related issue: #386527 (closed)

Screenshots or screen recordings

We can see the truncate_table! helper method ran correctly here (see !117373 (comment 1349554775)):

(arm64) tkgl2:gitlab tkuah$ be rails db:migrate:down:main VERSION=20230329091107
main: == [advisory_lock_connection] object_id: 274500, pg_backend_pid: 59826
main: == 20230329091107 TruncatePCiRunnerMachineBuilds: reverting ===================
main: == 20230329091107 TruncatePCiRunnerMachineBuilds: reverted (0.0142s) ==========

main: == [advisory_lock_connection] object_id: 274500, pg_backend_pid: 59826
(arm64) tkgl2:gitlab tkuah$ be rails db:migrate:down:ci VERSION=20230329091107
ci: == [advisory_lock_connection] object_id: 274440, pg_backend_pid: 59880
ci: == 20230329091107 TruncatePCiRunnerMachineBuilds: reverting ===================
ci: == 20230329091107 TruncatePCiRunnerMachineBuilds: reverted (0.0096s) ==========

ci: == [advisory_lock_connection] object_id: 274440, pg_backend_pid: 59880
(arm64) tkgl2:gitlab tkuah$ be rails db:migrate
main: == [advisory_lock_connection] object_id: 274700, pg_backend_pid: 59946
main: == 20230329091107 TruncatePCiRunnerMachineBuilds: migrating ===================
main: == 20230329091107 TruncatePCiRunnerMachineBuilds: migrated (0.1156s) ==========

main: == [advisory_lock_connection] object_id: 274700, pg_backend_pid: 59946
ci: == [advisory_lock_connection] object_id: 274880, pg_backend_pid: 59948
ci: == 20230329091107 TruncatePCiRunnerMachineBuilds: migrating ===================
ci: -- quote_table_name("p_ci_runner_machine_builds")
ci:    -> 0.0000s
ci: -- execute("TRUNCATE TABLE \"p_ci_runner_machine_builds\"")
ci:    -> 0.0062s
ci: == 20230329091107 TruncatePCiRunnerMachineBuilds: migrated (0.0139s) ==========

ci: == [advisory_lock_connection] object_id: 274880, pg_backend_pid: 59948

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Thong Kuah

Merge request reports