sql: gate potentially unsafe schema changes behind sql_safe_updates

[ajwerner]

Is your feature request related to a problem? Please describe.
There are some combinations of schema changes which can be hazardous and potentially cause data loss. Their are two classes of known problems to which this issue pertains. One, it can be problematic to drop columns which have constraints (see #47719). The second set of problems pertains to transactions which drop columns and perform other asynchronous schema changes which might fail. In those scenarios (#46541, #47712).

These situations are bad, but are not hard to avoid. Let's help guide our users to avoid them.

Describe the solution you'd like

I'd like the sql_safe_updates flag to prevent the two above problems. If you want to drop a column and you have sql_safe_updates enabled, then you need to drop its constraints first and you cannot drop it in the same transaction that adds columns with default or computed expressions or adds other constraints.

Describe alternatives you've considered
We should ultimately fix the underlying bugs but it's challenging.

[blathers-crl]

Hi @ajwerner, I've guessed the C-ategory of your issue and suitably labeled it. Please re-label if inaccurate.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[ajwerner]

I regret taking so long to test this in my implementation. I changed the implementation and then by the time I got to testing it I realized that all column drops are already blocked by sql_safe_updates and column drops are required in all of these cases :(. At least there's no time pressure on a backport now ...