want automated test to catch known kinds of problematic schema updates

[davepacheco]

Something like #6433 covers a type of schema update problem introduced in #6185 that is unfortunately too easy to do in SQL. Fortunately, a lot of these problematic patterns are known because they affect anybody trying to change the schema on a deployed system. We could have an automated test look for known kinds of problematic patterns and fail.

In this specific case, it seems reasonable to parse the ALTER TABLE SQL and blow up if we find it introducing a new constraint (which might not be true of existing rows). Developers could override this with an allowlist for cases where we know it's fine (e.g., the table is known to be empty, or previous software is known not to introduce rows that violate the constraint, etc.).

Some example issues we could look for (I haven't gone through these yet):
https://medium.com/paypal-tech/postgresql-at-scale-database-schema-changes-without-downtime-20d3749ed680
https://planetscale.com/blog/backward-compatible-databases-changes
https://xata.io/blog/postgres-schema-changes-pita
https://medium.com/preply-engineering/postgresql-schema-change-gotchas-bf904e2d5bb7
https://www.reddit.com/r/PostgreSQL/comments/zpm4c6/is_there_a_linter_for_nonbackwardscompatible_db/

This is a huge space and there's a lot there. But we can start small and add checks as it makes sense.

[smklein]

https://squawkhq.com/docs/adding-required-field looks like it could have caught this particular issue.

I haven't investigated https://github.com/sbdchd/squawk/tree/master enough to figure out if this is a tool we'd want to use, but at a distance it looks like "clippy for postgres schema changes".

[davepacheco]

This isn't exactly related to schema updates, but if we wind up making something to catch problematic schema design patterns, we should consider adding something to flag unique indexes on NULL-able columns -- see #8021 for a discussion of how this can go wrong.