Recognize patterns where better solutions exist

[kaaveland]

The documentation for https://github.com/ankane/strong_migrations is awesome and it might be possible to recognize a bunch of the same things, either by parsing the sql with a proper parser, or by inspecting the locks and potentially generate some recommendations or "fixing up" the migration programmatically.

[kaaveland]

It seems like the easiest way to do this is to snapshot queries against pg_attribute and pg_constraint, similarly to how we snapshot pg_class. We'd want to track:

• CHECK-constraint and whether they are VALID
• FOREIGN KEY-contraints and whether they are VALID
• The type, name and nullability of every column in non-system tables.
• All columns that take part in an index
• All columns that take part in a unique-index

This should let us match against these patterns from the strong migrations documentation:

• Changing the type of a column (causing table rewrite)
• Adding serial/bigserial column (causing table rewrite)
• Adding a stored generated column (causing table rewrite)
• Adding a VALID check constraint
• Adding a VALID foreign key
• Adding a unique constraint
• Adding a json column
• Changing a column to NOT NULL

[kaaveland]

type_change_requires_table_rewrite needs to be fixed to actually understand what type changes that forces a rewrite.

[kaaveland]

We should definitely make it possible to "fail the trace" if we find some of these patterns, because at that point, we know of better ways to do the migration.

[kaaveland]

We need to track indexes too, so we can recommend to add them concurrently. There's also a nice hint we could give if people add a unique constraint, where it's safer if they create a unique index concurrently.

[kaaveland]

Altering a check constraint body is probably often not safe. 🤔

[kaaveland]

32 brings us up to 8 patterns/rules, which is enough for me to close this one.