签到成功

知道了

CNDBA社区CNDBA社区

OpenGauss 中的 系统表 和 系统视图 说明

2023-03-11 00:19 980 0 原创 openGauss
作者: dave

1 系统表和视图说明

系统表是openGauss存放结构元数据的地方,它是openGauss数据库系统运行控制信息的来源,是数据库系统的核心组成部分。系统视图提供了查询系统表和访问数据库内部状态的方法。

系统表和系统视图要么只对管理员可见,要么对所有用户可见。正常情况下不应该由用户手工修改系统表或系统视图,或者手工重命名系统表或系统视图所在的模式,而是由SQL语句关联的系统表操作自动维护系统表信息。

在官方文档的系统表和系统视图的说明中,指出了表是对所有用户可见还是只对初始化用户可见。必须以初始化用户身份登录才能查询只对初始化用户可见的表。

http://www.cndba.cn/dave/article/116482
http://www.cndba.cn/dave/article/116482

openGauss提供了以下类型的系统表和视图:
1.继承自PG的系统表和视图,这类系统表和视图具有PG前缀。
2.openGaussl新增的系统表和视图,这类系统表和视图具有GS前缀。

2 系统表

因为OpenGauss 是拿PG 9.2.4 来魔改的,所以PG的会继承这个版本的系统表和视图,官网的系统表可以从如下链接查看:

https://www.postgresql.org/docs/9.2/catalogs.html

从官网看,PG的系统表是72个,OpenGauss是107个,这也说明OpenGauss的魔改比较多。http://www.cndba.cn/dave/article/116482

这里可以通过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新增的系统表,关于这些系统表的详细说明,可以直接查看官方手册。

http://www.cndba.cn/dave/article/116482

3 系统视图

官网的系统视图可以从如下链接查看:

https://www.postgresql.org/docs/current/views.html

[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个系统视图。同样关于这些视图的详细说明,参考官方手册。

http://www.cndba.cn/dave/article/116482
http://www.cndba.cn/dave/article/116482http://www.cndba.cn/dave/article/116482

4 常用的系统表和系统视图

虽然系统表和系统视图较多,但常用的只有那么几个,列举如下。

http://www.cndba.cn/dave/article/116482
http://www.cndba.cn/dave/article/116482

常用的系统表有:

pg_class
pg_attribute
pg_attrdef
pg_authid
pg_auth_members
pg_constraint
pg_tablespace
pg_namespace
pg_database
pg_index
pg_statistic

常用的系统视图有:

http://www.cndba.cn/dave/article/116482

pg_tables
pg_indexes
pg_views
pg_user
pg_roles
pg_rules
pg_settings

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2239
    原创
  • 3
    翻译
  • 547
    转载
  • 186
    评论
  • 访问:6666495次
  • 积分:4249
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ