frontend: refactor source schema resolution

[BugenZhao]

Generally, there're multiple sources of truth for the catalog derived from a CREATE SOURCE or CREATE TABLE statement.

• Column definitions and the constraints on them. key INT PRIMARY KEY
• Table definitions. PRIMARY KEY (key)
• Properties along with the row format. ROW SCHEMA LOCATION '..'

Given a parsed CREATE statement, it seems not that intuitive to decide the order of the steps for resolving each of them. In the current implementation, we...

1. bind column definitions without constraints
2. bind PRIMARY KEY column constraints
3. bind PRIMARY KEY table constraint
4. generate a row_id column if necessary
5. resolve source schema
   • check if valid according to the connector type
   • may add extra columns (for example, debezium mongo json)
   • may direct overwrite or discard the binding results so far (😨), according to the connector type
6. bind GENERATED column constraints (since we just have all columns resolved)

It's obvious to see that the procedure is somehow complicated and can be confusing, especially for step 5 which is too ad-hoc. Here I propose we let step 5 directly operate on the AST structure ahead of time, making all other binding steps work on an immutable and final CREATE statement AST. The advantages could be...

• split the logic of resolving source schema and binding columns completely, making life much easier (feat(frontend): print partial explain traces if there's error #9513, fix(frontend): skip generated columns when checking schema of nexmark connector #9584, and so on..)
• avoid manipulating columns manually, which bypasses ColumnIdGenerator and can be problematic if we support ALTER TABLE with connector (fix(frontend): dedup column ids (close #7905) #7972 (comment))
• normalize data types from all external systems into the types which are able to be represented in PostgreSQL syntax, to avoid compatibility issues
• based on the above, further support features like SHOW CREATE TABLE my_external_catalog.my_table (discussion: Support creating lakehouse catalog in risingwave. #8603)
• unparse back to a "pure" SQL, so that we can persist the definition and reuse it in the future safely

Feel free to comment. cc @tabVersion @st1page @xiangjinwu @yuhao-su

[st1page]

LGTM

[st1page]

before we determine use AST structure as the interface of the source schema resolution, I'd like to do some refactor on the function and try to make it better. 🤔

[st1page]

progress:

• refactor(binder): bind create table #10307
• refactor(binder): refactor the logic to valid constraints #10192
• refactor(binder): refine resolve source schema #10195

[xxchan]

Is it finished?

[st1page]

Is it finished?

I think not