sql: enable adding CHECK constraints in ALTER TABLE ADD COLUMN

[jseldess]

When adding a column to a table via ALTER TABLE ... ADD COLUMN, it's not currently possible to define a CHECK constraint on the new column:

[email protected]:60410/defaultdb> create table t1 (a int primary key);
CREATE TABLE

Time: 4.534737ms

[email protected]:60410/defaultdb> alter table t1 add column b int check (b > 0);
pq: adding a CHECK constraint via ALTER not supported

The ADD COLUMN docs currently only call out that you can't add a column with a foreign key constraint. All other constraints can be defined while adding a column.

I can update the docs to reflect the current behavior, but @knz suggested that it should be possible to add a column and define a check constraint on that column in a single statement.

[knz]

I have investigated this further. Although processing the descriptor change in ALTER is trivial, the column backfill logic must be extended to process CHECK constraints on newly populated values. Indeed, the following must produce an error:

ALTER TABLE foo ADD COLUMN bar INT DEFAULT 123 CHECK (bar < 0)

Without a CHECK validation in the backfill, it would be possible for a row to be populated with the (invalid) value 123.

[thoszhang]

#32504 is related to this - it will correctly handle validating the newly populated values in the case where the ADD COLUMN and ADD CONSTRAINT happen in the same transaction.

[knz]

Oh great! thank you!!

[knz]

@lucy-zhang are you taking Erik's PR over, given he left the org?

[thoszhang]

@knz yes, that's the plan