feat(stream): use table and column's description to imporve internal table's readability

[st1page]

We can add table and column's description on the internal state table. https://docs.risingwave.com/docs/current/sql-comment-on/

Currently:

dev=> create table t(x int, y int, z int);
CREATE_TABLE
dev=> explain create materialized view mv as select count(distinct x), max(y) from t group by z;
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 StreamMaterialize { columns: [count, max, t.z(hidden)], stream_key: [t.z], pk_columns: [t.z], pk_conflict: NoCheck }
 └─StreamProject { exprs: [count(distinct t.x), max(t.y), t.z] }
   └─StreamHashAgg { group_key: [t.z], aggs: [count(distinct t.x), max(t.y), count] }
     └─StreamExchange { dist: HashShard(t.z) }
       └─StreamTableScan { table: t, columns: [x, y, z, _row_id] }
(5 rows)

dev=> create materialized view mv as select count(distinct x), max(y) from t group by z;
CREATE_MATERIALIZED_VIEW
dev=> show internal tables;
                  Name                  
----------------------------------------
 __internal_mv_9_hashaggstate_15
 __internal_mv_9_hashaggdedupforcol0_16
 __internal_mv_10_streamscan_17
 __internal_mv_9_hashaggcall1_14
(4 rows)

dev=> describe __internal_mv_9_hashaggstate_15
dev-> ;
        Name         |              Type               | Is Hidden | Description 
---------------------+---------------------------------+-----------+-------------
 t_z                 | integer                         | false     | 
 count(distinct t_x) | bigint                          | false     | 
 max(t_y)            | integer                         | false     | 
 count               | bigint                          | false     | 
 primary key         | t_z                             |           | 
 distribution key    | t_z                             |           | 
 table description   | __internal_mv_9_hashaggstate_15 |           | 
(7 rows)

dev=> describe __internal_mv_9_hashaggdedupforcol0_16;
         Name         |                  Type                  | Is Hidden | Description 
----------------------+----------------------------------------+-----------+-------------
 t_z                  | integer                                | false     | 
 t_x                  | integer                                | false     | 
 count_for_agg_call_0 | bigint                                 | false     | 
 primary key          | t_z, t_x                               |           | 
 distribution key     | t_z                                    |           | 
 table description    | __internal_mv_9_hashaggdedupforcol0_16 |           | 
(6 rows)

1. we can add table comments and show it in the show internal tables

dev=> show internal tables;
                  Name                  |                description             
----------------------------------------+------------------------------------------------------
 __internal_mv_9_hashaggstate_15        |  the agg's result for the node StreamHashAgg { group_key: [t.z], aggs: [count(distinct t.x), max(t.y), count] }
 __internal_mv_9_hashaggdedupforcol0_16 |  the distinct table of the column "t.x" for the node StreamHashAgg { group_key: [t.z], aggs: [count(distinct t.x), max(t.y), count] }
 __internal_mv_10_streamscan_17         |  the state table to maintain the backfill process of the node StreamTableScan { table: t, columns: [x, y, z, _row_id] }
 __internal_mv_9_hashaggcall1_14        |  the ordered table to maintain the result of "max(t.y)" result for the node StreamHashAgg { group_key: [t.z], aggs: [count(distinct t.x), max(t.y), count] }
(4 rows)

2. we can add some column description for the internal table

dev=> describe __internal_mv_9_hashaggdedupforcol0_16;
         Name         |                  Type                  | Is Hidden | Description 
----------------------+----------------------------------------+-----------+-------------
 t_z                  | integer                                | false     | group key
 t_x                  | integer                                | false     | distinct key
 count_for_agg_call_0 | bigint                                 | false     | count with no filter
 primary key          | t_z, t_x                               |           | 
 distribution key     | t_z                                    |           | 
 table description    | __internal_mv_9_hashaggdedupforcol0_16 |           | the distinct table of the column "t.x" for the node StreamHashAgg { group_key: [t.z], aggs: [count(distinct t.x), max(t.y), count] }

[github-actions]

This issue has been open for 60 days with no activity.

If you think it is still relevant today, and needs to be done in the near future, you can comment to update the status, or just manually remove the no-issue-activity label.

You can also confidently close this issue as not planned to keep our backlog clean.
Don't worry if you think the issue is still valuable to continue in the future.
It's searchable and can be reopened when it's time. 😄