sql/schemachanger: generalize statement-time execution

[ajwerner]

Is your feature request related to a problem? Please describe.
In the declarative schema changer, we want to update the state of the world as observed by subsequent statements in the current transaction. Some of the logical side-effects we would like to make visible to the current transaction immediately, but would be dangerous or wrong to publish to concurrent transactions eagerly.

We have logic like this in a limited form today. When dropping descriptors, we drop them "synthetically". This TXN_DROPPED state will store the descriptors in the DROP state in the descriptor collection's synthetic set. This means that queries and virtual tables will observe the descriptor as being dropped, but the descriptor will not have been written to the key-value store as being dropped. Instead, we can defer the choice of whether or not to write the dropped descriptor until PreCommitPhase, when we know whether any part of the schema change might fail.

Consider the case of dropping a default expression followed by adding a constraint. Subsequent statements in the transaction, ideally, would no longer observe the old default expression. However, if the constraint addition subsequently (asynchronously) fails, no concurrent transactions should have observed the dropping of the default value.

Describe the solution you'd like

Rather than explicitly modeling the synthetic application of a state change as we did in TXN_DROPPED, what we ought to do is to synthetically apply side-effects during StatementPhase such that all mutations applied during statement phase affect only synthetic descriptors. This way, the synthetic state of the schema change diverges from the true state. At each schema change statement, we plan as though no operations have yet occurred to the descriptors, and we re-apply side-effects to create a new set of synthetic descriptors. This way, by the time we go to perform real side-effects on the descriptors, we have full knowledge of all of the operations in the schema change.

Epic: CRDB-19158

Jira issue: CRDB-19763

[postamar]

Andrew and I discussed this offline.

Presently what we do is this:

1. at statement execution time, we plan the schema change and execute the ops in the first stage only, which is always a mutation stage;
2. these ops may update synthetic descriptors (certainly when it's a DROP statement) and because it's a mutation stage the execution always concludes by executing a kv.Batch full of Puts to system.descriptor and friends.
3. goto (1) if the transaction is explicit and there are subsequent DDL statements;
4. at pre-commit time, we plan the schema change and run the pre-commit phase mutation stage, after which the schema change job takes over.

The approach to the solution I propose is as follows:

1. at statement execution time, plan the schema change and execute all mutation stage ops, this brings the set of uncommitted descriptors in the descs.Collection to a state which mirrors the terminal schema change state.
2. Let's not actually run these kv.Batches! The changes are all in-memory and local to the transaction, and they'll be seen by any DML statement, which is what we want.
3. If this is an explicit transaction and we encounter a subsequent DDL statement, we wipe the uncommitted descriptors layer and then goto (1).
4. At pre-commit time, we do the same thing except instead of goto (1) we plan the schema change and execute the pre-commit mutation stage normally (running the batches, etc) and then let the job take over.

This way, the concept of "statement phase" effectively disappears and only scexec and scrun need to care about whether we're in an explicit transaction or not, and the difference comes down to persisting and publishing the side-effects or not. So, surprisingly enough this might actually simplify things!

There is a missing piece to this puzzle: currently we only have this concept of uncommitted schema changes for descriptors, we'd need to extend that to the rest of the schema; so: namespace, comment, zone entries. We wanted to do that anyway so that's fine.

[ajwerner]

The clear next step is to properly abstract these descriptor-associated concepts into the catalog to allow for in-memory side-effects to be observed.

[ajwerner]

One case that @ecwall brought up that's interesting is the case of DROP COLUMN. Today, in the statement phase, we move the column to DELETE_AND_WRITE_ONLY. We do this before we've begun to construct the new primary index. We do that because it's what the legacy schema changer did, but it's not great. Ideally we'd find a way to synthetically move the column to DELETE_AND_WRITE_ONLY for the current transaction, but to actually defer moving the column from PUBLIC for other transactions until the moment the schema change has become unrevertible.

I think this alone motivates use of synthetic descriptors during statement evaluation. More generally, I think there are a number of cases where the view we want DMLs in the current transaction to have a different view of descriptors than what we'll want to write for concurrent transactions to see.

[postamar]

That makes sense. I guess we agree that we want an in-memory txn-local view of descriptors; you're saying let's use the synthetic layer in the collection, I'm saying let's use the uncommitted layer. My reasoning is that it'll be easier to implement, but it doesn't really matter which layer we use as long as the collection does what we want.

[chengxiong-ruan]

So, from what I'm understanding that, with the generic statement-time execution, we replan and execute every proceeding DDL statement before planning current DDL statement. And IIUC, the replan is basically looking at statements only, and don't really care about the targets generated from proceeding plannings. This might be interesting for CREATEs that the object IDs could change when planning later statements. For example, we want to execute the following statements, assuming we have a existing table t(a INT):

(1) CREATE FUNCTION f_plus_one(a INT) RETURNS INT LANGUAGE SQL AS $$ SELECT a + 1 $$;
(2) ALTER TABLE t ADD COLUMN b INT DEFAULT f_plus_one(1023);

when statement (1) is first plan, we need to allocate a descriptor id for the function, then we clear the uncommitted layer before planning statement (2). To make the new UDF visible to statement (2), we need to replan statement (1). So here, if we don't cache the function id somewhere, we'd allocate a new id for the function, which is fine since we never flush the new function descriptor to kv, and statement (2) can just use the second allocated function id. The only thing is that it could be fun when debugging because the ID of newly created objects changes over later statements.

For rollback, I think it's also fine because we'd have the descriptor id in job payload.

[ajwerner]

And IIUC, the replan is basically looking at statements only, and don't really care about the targets generated from proceeding plannings.

This doesn't align with what I envision. The idea is that we'll never redo the build stage; we retain the targets from the previous statements. Let me do out an explicit example.

Consider:

CREATE TABLE foo (i INT PRIMARY KEY, j INT, INDEX(j));

BEGIN; 
ALTER TABLE foo DROP COLUMN j CASCADE; -- 1
SELECT * FROM foo; -- don't want to see j
ALTER TABLE foo ADD COLUMN k INT;
COMMIT;

The idea is that in step 1, we'll first parse and build targets. In this case, we're going to have column j targetting absent and we're also going to get rid of the index etc. Today, during statement phase, we transition that column to WRITE_ONLY.

   │         ├── 3 elements transitioning toward PUBLIC
   │         │    ├── ABSENT → BACKFILL_ONLY PrimaryIndex:{DescID: 114, IndexID: 3, ConstraintID: 2, TemporaryIndexID: 4, SourceIndexID: 1}
   │         │    ├── ABSENT → PUBLIC        IndexColumn:{DescID: 114, ColumnID: 1, IndexID: 3}
   │         │    └── ABSENT → PUBLIC        IndexData:{DescID: 114, IndexID: 3}
   │         ├── 2 elements transitioning toward TRANSIENT_ABSENT
   │         │    ├── ABSENT → DELETE_ONLY   TemporaryIndex:{DescID: 114, IndexID: 4, ConstraintID: 3, SourceIndexID: 1}
   │         │    └── ABSENT → PUBLIC        IndexColumn:{DescID: 114, ColumnID: 1, IndexID: 4}
   │         ├── 4 elements transitioning toward ABSENT
   │         │    ├── PUBLIC → WRITE_ONLY    Column:{DescID: 114, ColumnID: 2}
   │         │    ├── PUBLIC → ABSENT        ColumnName:{DescID: 114, Name: j, ColumnID: 2}
   │         │    ├── PUBLIC → VALIDATED     SecondaryIndex:{DescID: 114, IndexID: 2, ConstraintID: 0}
   │         │    └── PUBLIC → ABSENT        IndexName:{DescID: 114, Name: foo_j_idx, IndexID: 2}

Today, during statement execution, we will literally write the descriptor with the column j in WRITE_ONLY.

Now, the idea is that with this proposal, we'll still move that column to WRITE_ONLY in STATEMENT but it'll only be moved synthetically. When the next schema change statement comes around, we'll revert the synthetic side-effects and we'll also move the current status of all of the schema changer state back to their initial status.

When the second statement comes along, it'll get built and we'll have new targets (note that the builder sees the targets from the previous statements). Then, we'll plan with the targets from the union of the two statements and execute that synthetically. This will mean again transitioning the column to WRITE_ONLY.

The key insight here is that this lets us, at commit time, act as though there was only a single DDL statement when we decide what side-effects we actually want to publish.

Hope this helps. Happy to go through more details.