Ensure faithful duplication of columns for backfilling

[andrew-farries]

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 CHECK constraint.
• add a FOREIGN KEY constraint
• add a UNIQUE constraint

The information that needs to be preserved when duplicating a column for backfilling is:

• columnDEFAULT.
• 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	🟢	🟢	🟢	🟢	🟢	🟢	[1]
column nullability	n/a	n/a	🟢	🟢	🟢	🟢	🟢
column comments	🟢	🟢	🟢	🟢	🟢	🟢	🟢

[1] - It isn't currently possible to create unique constraints covering more than one column so it's not possible to test that existing UNIQUE constraints on a column are preserved when adding another.