Run DDL in the background

[chenzl25]

Is your feature request related to a problem? Please describe.

Some users want to run DDLs (usually creating a materialized view) in the background. Now we have provided a system table to show the progress of DDLs. If we plan to
To run DDLs in the background, once these DDLs are validated, our system needs to guarantee its completeness (even if the cluster is down during execution of the DDL) unless a cancel statement is issued.
It means we need to persist the materialized view meta (invisible first) and its backfill progress.If backfilling is finished, we can make the materialized view visible.

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response

[BugenZhao]

Related:

• ddl: cancel with SQL command #8032
• Discussion: when should create MV/sink return OK? #7593

[TennyZhuang]

I think it shouldn't be a SQL feature.

Not only the DDLs may be slow, but also DMLs or DQLs, e.g.

• INSERT ... SELECT ... with a huge table.
• A batch query with large table scans and several joins.

I proposed to introduce a new background SQL execution mode.

What's the difference?

The normal SQL execution mode:

1. User submitted the query
2. Query returned the result set and completed.

The background SQL execution mode:

1. User submitted the query
2. The server persisted the query and returned ACK.
3. The server ran the query in the background.
4. User retrieved the resultset by polling.

[TennyZhuang]

Anyway, at current time, only allow DDLs to be executed in the background mode is accepted to me.

[liurenjie1024]

Another thing is fault tolerance of background executions. For me, it would be more natural to make it fault tolerant.

[kwannoel]

@yezizp2012 are you planning to take this up as an extension of #8145 ? If not I'd like to work on it.

[yezizp2012]

@yezizp2012 are you planning to take this up as an extension of #8145 ? If not I'd like to work on it.

Not really, just feel free to assign yourself and take it. 😊

[github-actions]

This issue has been open for 60 days with no activity. Could you please update the status? Feel free to continue discussion or close as not planned.

[kwannoel]

Future changes for ddl in background after #9752 is merged:

• Scaling logic in meta service + progress report to vnode granularity: feat(backfill): Persist backfill operator state #9752 (review).
• Handling mix of partially complete and full complete states in backfill executor.
• Resuming of partially complete states in backfill executor.
• Persisting partially complete state to meta.
• Test coverage for recovery scenarios: feat(backfill): Persist backfill operator state #9752 (comment)

---

• We can ban scaling-in ddl which is undergoing backfill.
• That way we just need to persist state, which is good enough.
• Need to make sure to delete meta store state, if job is cancelled.

---

• Update table catalog to include creating_status, so meta service is able to filter only CREATED tables when notifying frontend. feat(meta): Add creating_status field for stream jobs #12330
• Update stream job status on finish: feat(meta): update StreamJob status on finish #12342
• Persist the table meta data in meta.
• Clean up the table meta data in meta.
• Support background ddl in frontend for materialized view. feat(frontend): support background ddl for materialized views #12355
• Support sink
• Support and index.