want automated test to catch known kinds of problematic schema updates

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.

Start by reviewing issues #6433 and #6185, then read the linked material on backward-compatible PostgreSQL schema changes. Define a small initial set of ALTER TABLE patterns and the allowlist behavior; done means an automated check can detect those patterns and permit explicitly approved exceptions.