Backfill new columns with values ​​from Go code

Question content

What is the best way to backfill a new column with values ​​from Go application code? Ideally, I would loop through each row, call the Go code, and use the return value as the column value. Go code is stateful. That is, it acts on the data structure that is updated on each new row and calculates a value based on this information.

What I'm thinking of doing is doing a migration, adding the new columns as nullable, and then running a Go program after the migration to backfill the rows. But it would be better if all this could be done in one migration, especially since the column should not be NULL in the first place.

We use Flyway for migration. Our database is Postgres 11.5.

Correct answer

It is not possible to populate new columns in this way via migration - that is, in one step within a transaction. Instead, you need something like:

1. Update the application to properly handle rows in new columns with marked values ​​before timestamp 123.
2. Add new columns with non-null constraints and default tag values ​​in the migration.
3. Run the go program and update the sentinel value of the row before timestamp 123 to the appropriate value.

This method requires changes to the application but does not require a maintenance window. It also ensures that migrations do not fail because constraints are not satisfied at the time of migration.

The above is the detailed content of Backfill new columns with values ​​from Go code. For more information, please follow other related articles on the PHP Chinese website!