Ensure faithful duplication of columns for backfilling #227
Description
Various pgroll operations need to duplicate (and backfill) a column on migration start. The general pattern for these operations is:
On migration start:
- Create a duplicate of the affected column
- Backfill the duplicated column with values from the original.
On migration complete:
- Drop the original column
- Rename the duplicated column to the original column name
Duplication of columns currently loses a lot of information about the source column. These means that when such a migration completes, information (such as constraints and defaults) on the original column is lost. Currently only the column's type is preserved.
The operations that duplicate a column are:
- set
NOT NULL. - change type.
- drop a constraint.
- add a
CHECKconstraint. - add a
FOREIGN KEYconstraint - add a
UNIQUEconstraint
The information that needs to be preserved when duplicating a column for backfilling is:
- column
DEFAULT. - foreign keys defined on the column.
- indexes defined on the column.
- constraints defined on the column.
- nullability
Support matrix
For each property of a column, p, and each operation type, o, we need to preserve property p when running operation o:
| property/operation | set NOT NULL |
drop NOT NULL |
change type | drop constraint | add CHECK constraint |
add FK constraint |
add UNIQUE constraint |
|---|---|---|---|---|---|---|---|
column DEFAULT |
🟢 | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 |
| FKs on column | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 |
| indexes on column | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 |
| check constraints on column | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 |
| unique constraints on column | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 |
| column nullability | n/a | n/a | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 |
| column comments | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 | 🟢 |