Partitioning: Add migration helpers for updating sync functions
While performing the partitioning for Audit Events, we had a lot of updates on the schema of the original audit_events
table.
As the process of backfilling the partitions with the data from the original table, keeping them in sync and adding any missing indexes to the partitions takes considerable time for large tables, we don't want to block (most) schema updates.
That means that any migration that adds or removes columns to the original table, has to also update the sync function that is used on the trigger responsible for keeping the original table and the partitions in sync.
As an example for audit_events
, we had at least the following migrations updating the schema and the sync function:
- Remove updated_at column from audit_events table
- Add target_type column to audit_events table
- Add target_id column to audit_events table
- Remove type column from audit_events table
Each one has to manually CREATE OR REPLACE
the sync function both for the up and the down part of the migration.
That's a manual process that's prone to both copy/paste errors and errors generated when serializing MRs as @tancnle spotted:
| MR 1 (base on trigger function in master): remove field A in the trigger function
| MR 2 (base on trigger function in master): remove field B in the trigger function
|
| MR 1: merged first <--- field A has been removed from the trigger function
| MR 2: merge second <--- field B has been removed but field A is available in this version of the trigger function
| MR 2: roll back <--- Also wrong trigger function as field A is still available
|
v Time
As this seems like an operation that we may have to do multiple more times as we move forward with more partitioning endeavors, we should maybe:
- Add migration helpers for declaratively adding/removing columns to sync functions
- Add a spec template for checking that a released function is the same as the one in
structure.sql
Rough sketch of a possible solution:
We can add some generic PartitioningMigrationHelpers
for adding/removing columns to partitioning sync triggers that will follow a similar approach to the one that initially defines this trigger:
- Find the currently defined column names
- Add/remove the column in the migration depending on the helper called (e.g.
add_column_to_sync_trigger(source_table_name, unique_key)
) - Rebuild the trigger from scratch with
replace: true