CNDBA社区
1 系统表和视图说明
系统表是openGauss存放结构元数据的地方，它是openGauss数据库系统运行控制信息的来源，是数据库系统的核心组成部分。系统视图提供了查询系统表和访问数据库内部状态的方法。
系统表和系统视图要么只对管理员可见，要么对所有用户可见。正常情况下不应该由用户手工修改系统表或系统视图，或者手工重命名系统表或系统视图所在的模式，而是由SQL语句关联的系统表操作自动维护系统表信息。
在官方文档的系统表和系统视图的说明中，指出了表是对所有用户可见还是只对初始化用户可见。必须以初始化用户身份登录才能查询只对初始化用户可见的表。
openGauss提供了以下类型的系统表和视图：
1．继承自PG的系统表和视图，这类系统表和视图具有PG前缀。
2．openGaussl新增的系统表和视图，这类系统表和视图具有GS前缀。
2 系统表
因为OpenGauss 是拿PG 9.2.4 来魔改的，所以PG的会继承这个版本的系统表和视图，官网的系统表可以从如下链接查看：
从官网看，PG的系统表是72个，OpenGauss是107个，这也说明OpenGauss的魔改比较多。
这里可以通过pg_class 系统表来查看，我们这里直接在gsql里面执行/d 来查看：
[dave@www.cndba.cn ~]$ gsql -d postgres -p 15400
Password:
gsql ((openGauss 3.0.3 build 46134f73) compiled at 2023-01-10 22:42:07 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=#
openGauss=# /dt pg_*
List of relations
Schema | Name | Type | Owner | Storage
------------+------------------------------+-------+-------+---------
pg_catalog | pg_aggregate | table | omm |
pg_catalog | pg_am | table | omm |
pg_catalog | pg_amop | table | omm |
pg_catalog | pg_amproc | table | omm |
pg_catalog | pg_app_workloadgroup_mapping | table | omm |
pg_catalog | pg_attrdef | table | omm |
pg_catalog | pg_attribute | table | omm |
pg_catalog | pg_auth_history | table | omm |
pg_catalog | pg_auth_members | table | omm |
pg_catalog | pg_authid | table | omm |
pg_catalog | pg_cast | table | omm |
pg_catalog | pg_class | table | omm |
pg_catalog | pg_collation | table | omm |
pg_catalog | pg_constraint | table | omm |
pg_catalog | pg_conversion | table | omm |
pg_catalog | pg_database | table | omm |
pg_catalog | pg_db_role_setting | table | omm |
pg_catalog | pg_default_acl | table | omm |
pg_catalog | pg_depend | table | omm |
pg_catalog | pg_description | table | omm |
pg_catalog | pg_directory | table | omm |
pg_catalog | pg_enum | table | omm |
pg_catalog | pg_extension | table | omm |
pg_catalog | pg_extension_data_source | table | omm |
pg_catalog | pg_foreign_data_wrapper | table | omm |
pg_catalog | pg_foreign_server | table | omm |
pg_catalog | pg_foreign_table | table | omm |
pg_catalog | pg_hashbucket | table | omm |
pg_catalog | pg_index | table | omm |
pg_catalog | pg_inherits | table | omm |
pg_catalog | pg_job | table | omm |
pg_catalog | pg_job_proc | table | omm |
pg_catalog | pg_language | table | omm |
pg_catalog | pg_largeobject | table | omm |
pg_catalog | pg_largeobject_metadata | table | omm |
pg_catalog | pg_namespace | table | omm |
pg_catalog | pg_object | table | omm |
pg_catalog | pg_obsscaninfo | table | omm |
pg_catalog | pg_opclass | table | omm |
pg_catalog | pg_operator | table | omm |
pg_catalog | pg_opfamily | table | omm |
pg_catalog | pg_partition | table | omm |
pg_catalog | pg_pltemplate | table | omm |
pg_catalog | pg_proc | table | omm |
pg_catalog | pg_publication | table | omm |
pg_catalog | pg_publication_rel | table | omm |
pg_catalog | pg_range | table | omm |
pg_catalog | pg_replication_origin | table | omm |
pg_catalog | pg_resource_pool | table | omm |
pg_catalog | pg_rewrite | table | omm |
pg_catalog | pg_rlspolicy | table | omm |
pg_catalog | pg_seclabel | table | omm |
pg_catalog | pg_shdepend | table | omm |
pg_catalog | pg_shdescription | table | omm |
pg_catalog | pg_shseclabel | table | omm |
pg_catalog | pg_statistic | table | omm |
pg_catalog | pg_statistic_ext | table | omm |
pg_catalog | pg_subscription | table | omm |
pg_catalog | pg_synonym | table | omm |
pg_catalog | pg_tablespace | table | omm |
pg_catalog | pg_trigger | table | omm |
pg_catalog | pg_ts_config | table | omm |
pg_catalog | pg_ts_config_map | table | omm |
pg_catalog | pg_ts_dict | table | omm |
pg_catalog | pg_ts_parser | table | omm |
pg_catalog | pg_ts_template | table | omm |
pg_catalog | pg_type | table | omm |
pg_catalog | pg_user_mapping | table | omm |
pg_catalog | pg_user_status | table | omm |
pg_catalog | pg_workload_group | table | omm |
(70 rows)
openGauss=# /dt gs_*
List of relations
Schema | Name | Type | Owner | Storage
------------+-------------------------------+-------+-------+----------------------------------
pg_catalog | gs_asp | table | omm |
pg_catalog | gs_auditing_policy | table | omm |
pg_catalog | gs_auditing_policy_access | table | omm |
pg_catalog | gs_auditing_policy_filters | table | omm |
pg_catalog | gs_auditing_policy_privileges | table | omm |
pg_catalog | gs_client_global_keys | table | omm |
pg_catalog | gs_client_global_keys_args | table | omm |
pg_catalog | gs_column_keys | table | omm |
pg_catalog | gs_column_keys_args | table | omm |
pg_catalog | gs_db_privilege | table | omm |
pg_catalog | gs_encrypted_columns | table | omm |
pg_catalog | gs_encrypted_proc | table | omm |
pg_catalog | gs_global_chain | table | omm |
pg_catalog | gs_global_config | table | omm |
pg_catalog | gs_job_argument | table | omm |
pg_catalog | gs_job_attribute | table | omm |
pg_catalog | gs_masking_policy | table | omm |
pg_catalog | gs_masking_policy_actions | table | omm |
pg_catalog | gs_masking_policy_filters | table | omm |
pg_catalog | gs_matview | table | omm |
pg_catalog | gs_matview_dependency | table | omm |
pg_catalog | gs_model_warehouse | table | omm |
pg_catalog | gs_obsscaninfo | table | omm |
pg_catalog | gs_opt_model | table | omm |
pg_catalog | gs_package | table | omm |
pg_catalog | gs_policy_label | table | omm |
pg_catalog | gs_recyclebin | table | omm |
pg_catalog | gs_sql_patch | table | omm |
pg_catalog | gs_txn_snapshot | table | omm |
pg_catalog | gs_uid | table | omm |
pg_catalog | gs_wlm_ec_operator_info | table | omm | {orientation=row,compression=no}
pg_catalog | gs_wlm_instance_history | table | omm | {orientation=row,compression=no}
pg_catalog | gs_wlm_operator_info | table | omm | {orientation=row,compression=no}
pg_catalog | gs_wlm_plan_encoding_table | table | omm | {orientation=row,compression=no}
pg_catalog | gs_wlm_plan_operator_info | table | omm | {orientation=row,compression=no}
pg_catalog | gs_wlm_session_query_info_all | table | omm | {orientation=row,compression=no}
pg_catalog | gs_wlm_user_resource_history | table | omm | {orientation=row,compression=no}
(37 rows)
openGauss=#
这里gs_开头的系统表就是OpenGauss新增的系统表，关于这些系统表的详细说明，可以直接查看官方手册。
3 系统视图
官网的系统视图可以从如下链接查看：
[dave@www.cndba.cn ~]$ gsql -d postgres -p 15400
Password:
gsql ((openGauss 3.0.3 build 46134f73) compiled at 2023-01-10 22:42:07 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=#
openGauss=# /dv pg_*
List of relations
Schema | Name | Type | Owner | Storage
------------+---------------------------------+------+-------+-------------------------
pg_catalog | pg_available_extension_versions | view | omm |
pg_catalog | pg_available_extensions | view | omm |
pg_catalog | pg_comm_delay | view | omm |
pg_catalog | pg_comm_recv_stream | view | omm |
pg_catalog | pg_comm_send_stream | view | omm |
pg_catalog | pg_comm_status | view | omm |
pg_catalog | pg_control_group_config | view | omm |
pg_catalog | pg_cursors | view | omm |
pg_catalog | pg_ext_stats | view | omm |
pg_catalog | pg_get_invalid_backends | view | omm |
pg_catalog | pg_get_senders_catchup_time | view | omm |
pg_catalog | pg_group | view | omm |
pg_catalog | pg_gtt_attached_pids | view | omm | {security_barrier=true}
pg_catalog | pg_gtt_relstats | view | omm | {security_barrier=true}
pg_catalog | pg_gtt_stats | view | omm | {security_barrier=true}
pg_catalog | pg_indexes | view | omm |
pg_catalog | pg_locks | view | omm |
pg_catalog | pg_node_env | view | omm |
pg_catalog | pg_os_threads | view | omm |
pg_catalog | pg_prepared_statements | view | omm |
pg_catalog | pg_prepared_xacts | view | omm |
pg_catalog | pg_publication_tables | view | omm |
pg_catalog | pg_replication_origin_status | view | omm |
pg_catalog | pg_replication_slots | view | omm |
pg_catalog | pg_rlspolicies | view | omm |
pg_catalog | pg_roles | view | omm |
pg_catalog | pg_rules | view | omm |
pg_catalog | pg_running_xacts | view | omm |
pg_catalog | pg_seclabels | view | omm |
pg_catalog | pg_session_iostat | view | omm |
pg_catalog | pg_session_wlmstat | view | omm |
pg_catalog | pg_settings | view | omm |
pg_catalog | pg_shadow | view | omm |
pg_catalog | pg_stat_activity | view | omm |
pg_catalog | pg_stat_activity_ng | view | omm |
pg_catalog | pg_stat_all_indexes | view | omm |
pg_catalog | pg_stat_all_tables | view | omm |
pg_catalog | pg_stat_bad_block | view | omm |
pg_catalog | pg_stat_bgwriter | view | omm |
pg_catalog | pg_stat_database | view | omm |
pg_catalog | pg_stat_database_conflicts | view | omm |
pg_catalog | pg_stat_replication | view | omm |
pg_catalog | pg_stat_subscription | view | omm |
pg_catalog | pg_stat_sys_indexes | view | omm |
pg_catalog | pg_stat_sys_tables | view | omm |
pg_catalog | pg_stat_user_functions | view | omm |
pg_catalog | pg_stat_user_indexes | view | omm |
pg_catalog | pg_stat_user_tables | view | omm |
pg_catalog | pg_stat_xact_all_tables | view | omm |
pg_catalog | pg_stat_xact_sys_tables | view | omm |
pg_catalog | pg_stat_xact_user_functions | view | omm |
pg_catalog | pg_stat_xact_user_tables | view | omm |
pg_catalog | pg_statio_all_indexes | view | omm |
pg_catalog | pg_statio_all_sequences | view | omm |
pg_catalog | pg_statio_all_tables | view | omm |
pg_catalog | pg_statio_sys_indexes | view | omm |
pg_catalog | pg_statio_sys_sequences | view | omm |
pg_catalog | pg_statio_sys_tables | view | omm |
pg_catalog | pg_statio_user_indexes | view | omm |
pg_catalog | pg_statio_user_sequences | view | omm |
pg_catalog | pg_statio_user_tables | view | omm |
pg_catalog | pg_stats | view | omm |
pg_catalog | pg_tables | view | omm |
pg_catalog | pg_tde_info | view | omm |
pg_catalog | pg_thread_wait_status | view | omm |
pg_catalog | pg_timezone_abbrevs | view | omm |
pg_catalog | pg_timezone_names | view | omm |
pg_catalog | pg_total_memory_detail | view | omm |
pg_catalog | pg_total_user_resource_info | view | omm |
pg_catalog | pg_total_user_resource_info_oid | view | omm |
pg_catalog | pg_user | view | omm |
pg_catalog | pg_user_mappings | view | omm |
pg_catalog | pg_variable_info | view | omm |
pg_catalog | pg_views | view | omm |
pg_catalog | pg_wlm_statistics | view | omm |
(75 rows)
openGauss=# /dv gs_*
List of relations
Schema | Name | Type | Owner | Storage
------------+-----------------------------------+------+-------+---------
pg_catalog | gs_all_control_group_info | view | omm |
pg_catalog | gs_auditing | view | omm |
pg_catalog | gs_auditing_access | view | omm |
pg_catalog | gs_auditing_privilege | view | omm |
pg_catalog | gs_cluster_resource_info | view | omm |
pg_catalog | gs_comm_proxy_thread_status | view | omm |
pg_catalog | gs_db_privileges | view | omm |
pg_catalog | gs_file_stat | view | omm |
pg_catalog | gs_get_control_group_info | view | omm |
pg_catalog | gs_gsc_memory_detail | view | omm |
pg_catalog | gs_instance_time | view | omm |
pg_catalog | gs_labels | view | omm |
pg_catalog | gs_lsc_memory_detail | view | omm |
pg_catalog | gs_masking | view | omm |
pg_catalog | gs_matviews | view | omm |
pg_catalog | gs_os_run_info | view | omm |
pg_catalog | gs_redo_stat | view | omm |
pg_catalog | gs_session_cpu_statistics | view | omm |
pg_catalog | gs_session_memory | view | omm |
pg_catalog | gs_session_memory_context | view | omm |
pg_catalog | gs_session_memory_detail | view | omm |
pg_catalog | gs_session_memory_statistics | view | omm |
pg_catalog | gs_session_stat | view | omm |
pg_catalog | gs_session_time | view | omm |
pg_catalog | gs_shared_memory_detail | view | omm |
pg_catalog | gs_sql_count | view | omm |
pg_catalog | gs_stat_session_cu | view | omm |
pg_catalog | gs_thread_memory_context | view | omm |
pg_catalog | gs_total_memory_detail | view | omm |
pg_catalog | gs_total_nodegroup_memory_detail | view | omm |
pg_catalog | gs_wlm_cgroup_info | view | omm |
pg_catalog | gs_wlm_ec_operator_history | view | omm |
pg_catalog | gs_wlm_ec_operator_statistics | view | omm |
pg_catalog | gs_wlm_operator_history | view | omm |
pg_catalog | gs_wlm_operator_statistics | view | omm |
pg_catalog | gs_wlm_plan_operator_history | view | omm |
pg_catalog | gs_wlm_rebuild_user_resource_pool | view | omm |
pg_catalog | gs_wlm_resource_pool | view | omm |
pg_catalog | gs_wlm_session_history | view | omm |
pg_catalog | gs_wlm_session_info | view | omm |
pg_catalog | gs_wlm_session_info_all | view | omm |
pg_catalog | gs_wlm_session_statistics | view | omm |
pg_catalog | gs_wlm_user_info | view | omm |
pg_catalog | gs_wlm_workload_records | view | omm |
(44 rows)
我们可以看到在OpenGauss中，也多了44个系统视图，共119个系统视图。同样关于这些视图的详细说明，参考官方手册。
4 常用的系统表和系统视图
虽然系统表和系统视图较多，但常用的只有那么几个，列举如下。
常用的系统表有：
pg_class
pg_attribute
pg_attrdef
pg_authid
pg_auth_members
pg_constraint
pg_tablespace
pg_namespace
pg_database
pg_index
pg_statistic
常用的系统视图有：
pg_tables
pg_indexes
pg_views
pg_user
pg_roles
pg_rules
pg_settings
版权声明：本文为博主原创文章，未经博主允许不得转载。
