Skip to content

Commit 4557dd0

Browse files
author
Eric Harmeling
committed
sql: add crdb_internal views for system statistics tables
This commit adds two new crdb_internal views: - crdb_internal.statement_statistics_persisted, which surfaces the system.statement_statistics table - crdb_internal.transaction_statistics_persisted, which surfaces the system.transaction_statistics table Epic: none Release note: Added two views to the crdb_internal catalog: crdb_internal.statement_statistics_persisted, which surfaces data in the persisted system.statement_statistics table, and crdb_internal.transaction_statistics_persisted, which surfaces the system.transaction_statistics table.
1 parent 9ed78bf commit 4557dd0

15 files changed

Lines changed: 1010 additions & 806 deletions

File tree

‎pkg/ccl/logictestccl/testdata/logic_test/crdb_internal_tenant‎

Lines changed: 85 additions & 83 deletions
Original file line numberDiff line numberDiff line change
@@ -22,89 +22,91 @@ DROP DATABASE crdb_internal
2222
query TTTTIT
2323
SHOW TABLES FROM crdb_internal
2424
----
25-
crdb_internal active_range_feeds table admin NULL NULL
26-
crdb_internal backward_dependencies table admin NULL NULL
27-
crdb_internal builtin_functions table admin NULL NULL
28-
crdb_internal cluster_contended_indexes view admin NULL NULL
29-
crdb_internal cluster_contended_keys view admin NULL NULL
30-
crdb_internal cluster_contended_tables view admin NULL NULL
31-
crdb_internal cluster_contention_events table admin NULL NULL
32-
crdb_internal cluster_database_privileges table admin NULL NULL
33-
crdb_internal cluster_distsql_flows table admin NULL NULL
34-
crdb_internal cluster_execution_insights table admin NULL NULL
35-
crdb_internal cluster_inflight_traces table admin NULL NULL
36-
crdb_internal cluster_locks table admin NULL NULL
37-
crdb_internal cluster_queries table admin NULL NULL
38-
crdb_internal cluster_sessions table admin NULL NULL
39-
crdb_internal cluster_settings table admin NULL NULL
40-
crdb_internal cluster_statement_statistics table admin NULL NULL
41-
crdb_internal cluster_transaction_statistics table admin NULL NULL
42-
crdb_internal cluster_transactions table admin NULL NULL
43-
crdb_internal cluster_txn_execution_insights table admin NULL NULL
44-
crdb_internal create_function_statements table admin NULL NULL
45-
crdb_internal create_schema_statements table admin NULL NULL
46-
crdb_internal create_statements table admin NULL NULL
47-
crdb_internal create_type_statements table admin NULL NULL
48-
crdb_internal cross_db_references table admin NULL NULL
49-
crdb_internal databases table admin NULL NULL
50-
crdb_internal default_privileges table admin NULL NULL
51-
crdb_internal feature_usage table admin NULL NULL
52-
crdb_internal forward_dependencies table admin NULL NULL
53-
crdb_internal gossip_alerts table admin NULL NULL
54-
crdb_internal gossip_liveness table admin NULL NULL
55-
crdb_internal gossip_network table admin NULL NULL
56-
crdb_internal gossip_nodes table admin NULL NULL
57-
crdb_internal index_columns table admin NULL NULL
58-
crdb_internal index_spans table admin NULL NULL
59-
crdb_internal index_usage_statistics table admin NULL NULL
60-
crdb_internal invalid_objects table admin NULL NULL
61-
crdb_internal jobs table admin NULL NULL
62-
crdb_internal kv_catalog_comments table admin NULL NULL
63-
crdb_internal kv_catalog_descriptor table admin NULL NULL
64-
crdb_internal kv_catalog_namespace table admin NULL NULL
65-
crdb_internal kv_catalog_zones table admin NULL NULL
66-
crdb_internal kv_dropped_relations view admin NULL NULL
67-
crdb_internal kv_node_liveness table admin NULL NULL
68-
crdb_internal kv_node_status table admin NULL NULL
69-
crdb_internal kv_store_status table admin NULL NULL
70-
crdb_internal leases table admin NULL NULL
71-
crdb_internal lost_descriptors_with_data table admin NULL NULL
72-
crdb_internal node_build_info table admin NULL NULL
73-
crdb_internal node_contention_events table admin NULL NULL
74-
crdb_internal node_distsql_flows table admin NULL NULL
75-
crdb_internal node_execution_insights table admin NULL NULL
76-
crdb_internal node_inflight_trace_spans table admin NULL NULL
77-
crdb_internal node_memory_monitors table admin NULL NULL
78-
crdb_internal node_metrics table admin NULL NULL
79-
crdb_internal node_queries table admin NULL NULL
80-
crdb_internal node_runtime_info table admin NULL NULL
81-
crdb_internal node_sessions table admin NULL NULL
82-
crdb_internal node_statement_statistics table admin NULL NULL
83-
crdb_internal node_tenant_capabilities_cache table admin NULL NULL
84-
crdb_internal node_transaction_statistics table admin NULL NULL
85-
crdb_internal node_transactions table admin NULL NULL
86-
crdb_internal node_txn_execution_insights table admin NULL NULL
87-
crdb_internal node_txn_stats table admin NULL NULL
88-
crdb_internal partitions table admin NULL NULL
89-
crdb_internal pg_catalog_table_is_implemented table admin NULL NULL
90-
crdb_internal ranges view admin NULL NULL
91-
crdb_internal ranges_no_leases table admin NULL NULL
92-
crdb_internal regions table admin NULL NULL
93-
crdb_internal schema_changes table admin NULL NULL
94-
crdb_internal session_trace table admin NULL NULL
95-
crdb_internal session_variables table admin NULL NULL
96-
crdb_internal statement_statistics view admin NULL NULL
97-
crdb_internal super_regions table admin NULL NULL
98-
crdb_internal system_jobs table admin NULL NULL
99-
crdb_internal table_columns table admin NULL NULL
100-
crdb_internal table_indexes table admin NULL NULL
101-
crdb_internal table_row_statistics table admin NULL NULL
102-
crdb_internal table_spans table admin NULL NULL
103-
crdb_internal tables table admin NULL NULL
104-
crdb_internal tenant_usage_details view admin NULL NULL
105-
crdb_internal transaction_contention_events table admin NULL NULL
106-
crdb_internal transaction_statistics view admin NULL NULL
107-
crdb_internal zones table admin NULL NULL
25+
crdb_internal active_range_feeds table admin NULL NULL
26+
crdb_internal backward_dependencies table admin NULL NULL
27+
crdb_internal builtin_functions table admin NULL NULL
28+
crdb_internal cluster_contended_indexes view admin NULL NULL
29+
crdb_internal cluster_contended_keys view admin NULL NULL
30+
crdb_internal cluster_contended_tables view admin NULL NULL
31+
crdb_internal cluster_contention_events table admin NULL NULL
32+
crdb_internal cluster_database_privileges table admin NULL NULL
33+
crdb_internal cluster_distsql_flows table admin NULL NULL
34+
crdb_internal cluster_execution_insights table admin NULL NULL
35+
crdb_internal cluster_inflight_traces table admin NULL NULL
36+
crdb_internal cluster_locks table admin NULL NULL
37+
crdb_internal cluster_queries table admin NULL NULL
38+
crdb_internal cluster_sessions table admin NULL NULL
39+
crdb_internal cluster_settings table admin NULL NULL
40+
crdb_internal cluster_statement_statistics table admin NULL NULL
41+
crdb_internal cluster_transaction_statistics table admin NULL NULL
42+
crdb_internal cluster_transactions table admin NULL NULL
43+
crdb_internal cluster_txn_execution_insights table admin NULL NULL
44+
crdb_internal create_function_statements table admin NULL NULL
45+
crdb_internal create_schema_statements table admin NULL NULL
46+
crdb_internal create_statements table admin NULL NULL
47+
crdb_internal create_type_statements table admin NULL NULL
48+
crdb_internal cross_db_references table admin NULL NULL
49+
crdb_internal databases table admin NULL NULL
50+
crdb_internal default_privileges table admin NULL NULL
51+
crdb_internal feature_usage table admin NULL NULL
52+
crdb_internal forward_dependencies table admin NULL NULL
53+
crdb_internal gossip_alerts table admin NULL NULL
54+
crdb_internal gossip_liveness table admin NULL NULL
55+
crdb_internal gossip_network table admin NULL NULL
56+
crdb_internal gossip_nodes table admin NULL NULL
57+
crdb_internal index_columns table admin NULL NULL
58+
crdb_internal index_spans table admin NULL NULL
59+
crdb_internal index_usage_statistics table admin NULL NULL
60+
crdb_internal invalid_objects table admin NULL NULL
61+
crdb_internal jobs table admin NULL NULL
62+
crdb_internal kv_catalog_comments table admin NULL NULL
63+
crdb_internal kv_catalog_descriptor table admin NULL NULL
64+
crdb_internal kv_catalog_namespace table admin NULL NULL
65+
crdb_internal kv_catalog_zones table admin NULL NULL
66+
crdb_internal kv_dropped_relations view admin NULL NULL
67+
crdb_internal kv_node_liveness table admin NULL NULL
68+
crdb_internal kv_node_status table admin NULL NULL
69+
crdb_internal kv_store_status table admin NULL NULL
70+
crdb_internal leases table admin NULL NULL
71+
crdb_internal lost_descriptors_with_data table admin NULL NULL
72+
crdb_internal node_build_info table admin NULL NULL
73+
crdb_internal node_contention_events table admin NULL NULL
74+
crdb_internal node_distsql_flows table admin NULL NULL
75+
crdb_internal node_execution_insights table admin NULL NULL
76+
crdb_internal node_inflight_trace_spans table admin NULL NULL
77+
crdb_internal node_memory_monitors table admin NULL NULL
78+
crdb_internal node_metrics table admin NULL NULL
79+
crdb_internal node_queries table admin NULL NULL
80+
crdb_internal node_runtime_info table admin NULL NULL
81+
crdb_internal node_sessions table admin NULL NULL
82+
crdb_internal node_statement_statistics table admin NULL NULL
83+
crdb_internal node_tenant_capabilities_cache table admin NULL NULL
84+
crdb_internal node_transaction_statistics table admin NULL NULL
85+
crdb_internal node_transactions table admin NULL NULL
86+
crdb_internal node_txn_execution_insights table admin NULL NULL
87+
crdb_internal node_txn_stats table admin NULL NULL
88+
crdb_internal partitions table admin NULL NULL
89+
crdb_internal pg_catalog_table_is_implemented table admin NULL NULL
90+
crdb_internal ranges view admin NULL NULL
91+
crdb_internal ranges_no_leases table admin NULL NULL
92+
crdb_internal regions table admin NULL NULL
93+
crdb_internal schema_changes table admin NULL NULL
94+
crdb_internal session_trace table admin NULL NULL
95+
crdb_internal session_variables table admin NULL NULL
96+
crdb_internal statement_statistics view admin NULL NULL
97+
crdb_internal statement_statistics_persisted view admin NULL NULL
98+
crdb_internal super_regions table admin NULL NULL
99+
crdb_internal system_jobs table admin NULL NULL
100+
crdb_internal table_columns table admin NULL NULL
101+
crdb_internal table_indexes table admin NULL NULL
102+
crdb_internal table_row_statistics table admin NULL NULL
103+
crdb_internal table_spans table admin NULL NULL
104+
crdb_internal tables table admin NULL NULL
105+
crdb_internal tenant_usage_details view admin NULL NULL
106+
crdb_internal transaction_contention_events table admin NULL NULL
107+
crdb_internal transaction_statistics view admin NULL NULL
108+
crdb_internal transaction_statistics_persisted view admin NULL NULL
109+
crdb_internal zones table admin NULL NULL
108110

109111
statement ok
110112
CREATE DATABASE testdb; CREATE TABLE testdb.foo(x INT)

‎pkg/cli/zip_test.go‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -97,8 +97,10 @@ table_name NOT IN (
9797
'table_spans',
9898
'tables',
9999
'cluster_statement_statistics',
100+
'statement_statistics_persisted',
100101
'cluster_transaction_statistics',
101102
'statement_statistics',
103+
'transaction_statistics_persisted',
102104
'transaction_statistics',
103105
'tenant_usage_details',
104106
'pg_catalog_table_is_implemented'

‎pkg/sql/crdb_internal.go‎

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -179,13 +179,15 @@ var crdbInternal = virtualSchema{
179179
catconstants.CrdbInternalSessionTraceTableID: crdbInternalSessionTraceTable,
180180
catconstants.CrdbInternalSessionVariablesTableID: crdbInternalSessionVariablesTable,
181181
catconstants.CrdbInternalStmtStatsTableID: crdbInternalStmtStatsView,
182+
catconstants.CrdbInternalStmtStatsPersistedTableID: crdbInternalStmtStatsPersistedView,
182183
catconstants.CrdbInternalTableColumnsTableID: crdbInternalTableColumnsTable,
183184
catconstants.CrdbInternalTableIndexesTableID: crdbInternalTableIndexesTable,
184185
catconstants.CrdbInternalTableSpansTableID: crdbInternalTableSpansTable,
185186
catconstants.CrdbInternalTablesTableLastStatsID: crdbInternalTablesTableLastStats,
186187
catconstants.CrdbInternalTablesTableID: crdbInternalTablesTable,
187188
catconstants.CrdbInternalClusterTxnStatsTableID: crdbInternalClusterTxnStatsTable,
188189
catconstants.CrdbInternalTxnStatsTableID: crdbInternalTxnStatsView,
190+
catconstants.CrdbInternalTxnStatsPersistedTableID: crdbInternalTxnStatsPersistedView,
189191
catconstants.CrdbInternalTransactionStatsTableID: crdbInternalTransactionStatisticsTable,
190192
catconstants.CrdbInternalZonesTableID: crdbInternalZonesTable,
191193
catconstants.CrdbInternalInvalidDescriptorsTableID: crdbInternalInvalidDescriptorsTable,
@@ -6276,6 +6278,43 @@ GROUP BY
62766278
},
62776279
}
62786280

6281+
// crdb_internal.statement_statistics_persisted view selects persisted statement
6282+
// statistics from the system table. This view is primarily used to query statement
6283+
// stats info by date range.
6284+
var crdbInternalStmtStatsPersistedView = virtualSchemaView{
6285+
schema: `
6286+
CREATE VIEW crdb_internal.statement_statistics_persisted AS
6287+
SELECT
6288+
aggregated_ts,
6289+
fingerprint_id,
6290+
transaction_fingerprint_id,
6291+
plan_hash,
6292+
app_name,
6293+
node_id,
6294+
agg_interval,
6295+
metadata,
6296+
statistics,
6297+
plan,
6298+
index_recommendations,
6299+
indexes_usage
6300+
FROM
6301+
system.statement_statistics`,
6302+
resultColumns: colinfo.ResultColumns{
6303+
{Name: "aggregated_ts", Typ: types.TimestampTZ},
6304+
{Name: "fingerprint_id", Typ: types.Bytes},
6305+
{Name: "transaction_fingerprint_id", Typ: types.Bytes},
6306+
{Name: "plan_hash", Typ: types.Bytes},
6307+
{Name: "app_name", Typ: types.String},
6308+
{Name: "node_id", Typ: types.Int},
6309+
{Name: "agg_interval", Typ: types.Interval},
6310+
{Name: "metadata", Typ: types.Jsonb},
6311+
{Name: "statistics", Typ: types.Jsonb},
6312+
{Name: "plan", Typ: types.Jsonb},
6313+
{Name: "index_recommendations", Typ: types.StringArray},
6314+
{Name: "indexes_usage", Typ: types.Jsonb},
6315+
},
6316+
}
6317+
62796318
var crdbInternalActiveRangeFeedsTable = virtualSchemaTable{
62806319
comment: `node-level table listing all currently running range feeds`,
62816320
// NB: startTS is exclusive; consider renaming to startAfter.
@@ -6474,6 +6513,33 @@ GROUP BY
64746513
},
64756514
}
64766515

6516+
// crdb_internal.transaction_statistics_persisted view selects persisted transaction
6517+
// statistics from the system table. This view is primarily used to query transaction
6518+
// stats info by date range.
6519+
var crdbInternalTxnStatsPersistedView = virtualSchemaView{
6520+
schema: `
6521+
CREATE VIEW crdb_internal.transaction_statistics_persisted AS
6522+
SELECT
6523+
aggregated_ts,
6524+
fingerprint_id,
6525+
app_name,
6526+
node_id,
6527+
agg_interval,
6528+
metadata,
6529+
statistics
6530+
FROM
6531+
system.transaction_statistics`,
6532+
resultColumns: colinfo.ResultColumns{
6533+
{Name: "aggregated_ts", Typ: types.TimestampTZ},
6534+
{Name: "fingerprint_id", Typ: types.Bytes},
6535+
{Name: "app_name", Typ: types.String},
6536+
{Name: "node_id", Typ: types.Int},
6537+
{Name: "agg_interval", Typ: types.Interval},
6538+
{Name: "metadata", Typ: types.Jsonb},
6539+
{Name: "statistics", Typ: types.Jsonb},
6540+
},
6541+
}
6542+
64776543
var crdbInternalDroppedRelationsView = virtualSchemaView{
64786544
schema: `
64796545
CREATE VIEW crdb_internal.kv_dropped_relations AS

0 commit comments

Comments
 (0)