The current DDL mechanism can be improved simplified. We should
Maintain a table ddl_log in the system shard along with the tables table, this records the last ddl submitted
When ddl is submitted we store it in the ddl_log and tables table, we do not update in memory state.
We broadcast notification to all nodes that ddl has been submitted.
Each node loads any ddl_log entries newly added, and attempts to apply them, this may fail, e.g. entity does not exist. This also occurs on the originating node once the broadcast has been received. When this is received the API call can return.
Each ddl_log entry has a sequence number. The current state should also maintain a sequence number reflecting last ddl_log entry applied.
Applying ddl by reading the log ensures each node applies ddl in the same order, this means we do not need the ddl lock
When a node starts, it reads the tables table - this is effectively a snapshot and means the node does not need to replay all ddl_log entries.
In case of missing broadcast, each node should intermittently (say every 5 seconds) broadcast its latest in memory sequence number to other nodes. On receipt, if different then ddl_log entries should be loaded
DDL commands must always return quickly. MV and index fill and loading initial state are long lived processes and should not be part of the DDL execution.
The current DDL mechanism can be improved simplified. We should