Avoid rescheduling migrations again by using a trigger if nulls are added

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

The following discussion from !126134 (merged) should be addressed:

We recently updated our database guideline for adding a NOT NULL constraint !126134 (diffs). See this comment to understand the reason for updating the guideline !126134 (comment 1500021393).

The downside of the updated guideline is that there's a possibility of adding records with NULL attributes again after the migration to fix records has been completed. Then we would need to fix the code where we're introducing nulls and then reschedule the migration and background migrations sometimes take weeks to complete. It would be better if we could just fix the code introducing null records and avoid rescheduling the migration. This happens because we don't add a NOT NULL NOT VALID constraint anymore it prevents updating existing records !126134 (comment 1500021393).

One way to solve this is to use a trigger. So the release process would look something like this:

  1. Release M
    1. Ensure $ATTRIBUTE value is being set at the application level
      1. Add default value using Rails attributes API so $ATTRIBUTE default value is set for new records.
      2. Update all places in the code where $ATTRIBUTE is explicitly being set to nil, if any, for new or existing records.
    2. Add a NOT NULL NOT VALID constraint and a database trigger to set the default value when null is passed and log such instances.
    3. Backfill $ATTRIBUTE value at the database level ("Add a post-deployment migration to fix the existing records.")
  2. Release M+1

    1. After the background migration is finished check if the trigger is printing the above logline. If yes, then something inside the Rails code is passing null fix that. Otherwise, continue with the below steps.

    2. If step 1 seems fine and the backfill from Release M was done via a batched background migration: add a post-deployment migration that checks if the batched background migration is completed.

    3. Add a validation for $ATTRIBUTE at the ActiveRecord level (all existing and new records should be valid)

    4. Add a post-deployment migration that adds the NOT NULL constraint to $ATTRIBUTE at the database level and remove the trigger.

As part of this issue:

  1. Verify that the above steps work.
  2. Find where can we check the Postgres logs for production to see if anything has been logged by the trigger or not.
  3. Update the guideline with the above process.

Sample code:

Edited by 🤖 GitLab Bot 🤖