openGauss每日一练第 12 天 |模式（schema）管理

openGauss每日一练第12天

今日目标：openGauss的逻辑结构：模式管理

模式管理包括为数据库创建模式、删除模式、查看和设置模式的搜索路径、查看模式中的信息。

openGauss的模式是对数据库做一个逻辑分隔。所有的数据库对象都建立在模式下面。

openGauss的模式和用户时弱绑定的，所谓的弱绑定是指虽然创建用户的同时会自动创建一个同名模式，但用户也可以单独创建模式，并且为用户指定其他的模式。

在一个数据库中，可以有多个模式。模式可以把一组对象组织在一起。这样组织机构有多少个应用，我们可以将数据库对象组织成几个模式；组织机构有几个部分，又可以为部门创建单独的模式。默认情况下，用户将访问数据库的public模式。

openGauss在创建一个新的数据库时，会自动创建一个public模式。当用户登录到该数据库时，如果没有特殊的指定，都是操作在public模式中的数据库对象。
默认情况下，用户新创建的表，位于public模式中

gsql常用命令

查看库

\l

查看表空间

\db

查看用户

\du

查看模式

\dn+

查看表

\dt //查看当前库所有的表 list tables

\dt <表名> //查看指定表的信息

查看模式的搜索路径

show search_path;

设置模式的搜索路径

会话级模式搜索顺序的优先级最高，用户级模式搜索顺序的优先级第2，数据库级模式搜索顺序的优先级最低。

• 会话级别（已登录数据库）

  set search_path to <模式名>

• 数据库级别设置模式搜索顺序

  ALTER DATABASE <数据库> SET SEARCH_PATH TO <模式名>;

• 用户级别 设置模式搜索顺序

  ALTER USER <用户名> SET SEARCH_PATH TO <模式名>;

information_schema

信息模式本身是一个名为information_schema的模式。这个模式自动存在于所有数据库中。信息模式由一组视图构成，它们包含定义在当前数据库中对象的信息。这个模式的拥有者是初始数据库用户，并且该用户自然地拥有这个模式上的所有特权，包括删除它的能力。

• information_schema.schemata

  The view schemata contains all schemas in the current database that are owned by a currently enabled role.

  Name	Data Type	Description
  catalog_name	sql_identifier	Name of the database that the schema is contained in (always the current database)
  schema_name	sql_identifier	Name of the schema
  schema_owner	sql_identifier	Name of the owner of the schema
  default_character_set_catalog	sql_identifier	Applies to a feature not available in PostgreSQL
  default_character_set_schema	sql_identifier	Applies to a feature not available in PostgreSQL
  default_character_set_name	sql_identifier	Applies to a feature not available in PostgreSQL
  sql_path	character_data	Applies to a feature not available in PostgreSQL

• information_schema.tables
  The view tables contains all tables and views defined in the current database. Only those tables and views are shown that the current user has access to (by way of being the owner or having some privilege).

  Name	Data Type	Description
  table_catalog	sql_identifier	Name of the database that contains the table (always the current database)
  table_schema	sql_identifier	Name of the schema that contains the table
  table_name	sql_identifier	Name of the table
  table_type	character_data	Type of the table: BASE TABLE for a persistent base table (the normal table type), VIEW for a view, FOREIGN TABLE for a foreign table, or LOCAL TEMPORARY for a temporary table
  self_referencing_column_name	sql_identifier	Applies to a feature not available in PostgreSQL
  reference_generation	character_data	Applies to a feature not available in PostgreSQL
  user_defined_type_catalog	sql_identifier	If the table is a typed table, the name of the database that contains the underlying data type (always the current database), else null.
  user_defined_type_schema	sql_identifier	If the table is a typed table, the name of the schema that contains the underlying data type, else null.
  user_defined_type_name	sql_identifier	If the table is a typed table, the name of the underlying data type, else null.
  is_insertable_into	yes_or_no	YES if the table is insertable into, NO if not (Base tables are always insertable into, views not necessarily.)
  is_typed	yes_or_no	YES if the table is a typed table, NO if not
  commit_action	character_data	Not yet implemented

系统表

• PG_DATABASE

  系统表存储关于可用数据库的信息。

  名称	类型	描述
  oid	oid	行标识符（隐含属性，必须明确选择）。
  datname	name	数据库名称。
  datdba	oid	数据库所有人，通常为其创建者。
  encoding	integer	数据库的字符编码方式。
  datcollate	name	数据库使用的排序顺序。
  datctype	name	数据库使用的字符分类。
  datistemplate	boolean	是否允许作为模板数据库。
  datallowconn	boolean	如果为假，则没有用户可以连接到这个数据库。这个字段用于保护template0数据库不被更改。
  datconnlimit	integer	该数据库上允许的最大并发连接数，-1表示无限制。
  datlastsysoid	oid	数据库里最后一个系统OID 。
  datfrozenxid	xid32	用于跟踪该数据库是否需要为了防止事务ID重叠而进行清理。当前版本该字段已经废弃使用，为保持前向兼容，保留此字段，新增datfrozenxid64用于记录此信息。
  dattablespace	oid	数据库的缺省表空间。
  datcompatibility	name	数据库兼容模式，当前支持四种兼容模式：A、B、C、PG，分别表示兼容O、MY、TD和POSTGRES。
  datacl	aclitem[]	访问权限。
  datfrozenxid64	xid	用于跟踪该数据库是否需要为了防止事务ID重叠而进行清理。
  datminmxid	xid	该数据库中中所有在这个之前的多事务ID已经被一个事务ID替换。这用于跟踪该数据库是否需要为了防止事务ID重叠或者允许收缩pg_clog而进行清理。它是此数据库中所有表的pg_class.relminmxid中的最小值。

• PG_TABLESPACE
  PG_TABLESPACE系统表存储表空间信息。

  名称	类型	描述
  oid	oid	行标识符（隐含属性，必须明确选择）。
  spcname	name	表空间名称。
  spcowner	oid	表空间的所有者，通常是创建它的人。
  spcacl	aclitem[]	访问权限。具体请参见[GRANT](mk:@MSITStore:F:\openGauss学习\openGauss-document-zh-3.0.0\openGauss 3.0.0 开发者指南（企业版）01.chm::/zh-cn_topic_0289900312.html)和[REVOKE](mk:@MSITStore:F:\openGauss学习\openGauss-document-zh-3.0.0\openGauss 3.0.0 开发者指南（企业版）01.chm::/zh-cn_topic_0289900263.html)。
  spcoptions	text[]	表空间的选项。
  spcmaxsize	text	可使用的最大磁盘空间大小，单位Byte。
  relative	boolean	标识表空间指定的存储路径是否为相对路径。

• pg_class

  PG_CLASS系统表存储数据库对象信息及其之间的关系

2.课后作业

2.1 创建一个名为testsm、testsm1的模式

omm=# create schema testsm;
CREATE SCHEMA
omm=# create schema testsm1;
CREATE SCHEMA
omm=# \dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 pkg_service     | omm
 public          | omm
 schm1           | user1
 schm2           | omm
 schm3           | omm
 snapshot        | omm
 sqladvisor      | omm
 testsm          | omm
 testsm1         | omm
 user1           | user1
(16 rows)

omm=# 

2.2 创建一个用户john, 并将testsm的owner修改为john，且修改owner前后分别使用\dn+查看模式信息

– 修改模式所有者

ALTER SCHEMA schema_name OWNER TO new_owner;

omm=# create user  john identified by 'gauss@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter user john sysadmin ;
ALTER ROLE
omm=# \dn+
                                         List of schemas
      Name       | Owner | Access privileges |           Description            | WithBlockChain 
-----------------+-------+-------------------+----------------------------------+----------------
 blockchain      | omm   |                   | blockchain schema                | f
 cstore          | omm   |                   | reserved schema for DELTA tables | f
 schm2           | omm   |                   |                                  | f
 schm3           | omm   |                   |                                  | f
 snapshot        | omm   |                   | snapshot schema                  | f
 sqladvisor      | omm   | omm=UC/omm       +| sqladvisor schema                | f
 db4ai           | omm   | omm=UC/omm       +| db4ai schema                     | f
                 |       | =U/omm            |                                  | 
 dbe_perf        | omm   |                   | dbe_perf schema                  | f
 dbe_pldebugger  | omm   | omm=UC/omm       +| dbe_pldebugger schema            | f
                 |       | =U/omm            |                                  | 
 dbe_pldeveloper | omm   | omm=UC/omm       +| dbe_pldeveloper schema           | f
                 |       | =U/omm            |                                  | 
 john            | john  |                   |                                  | f
 pkg_service     | omm   |                   | pkg_service schema               | f
 public          | omm   | omm=UC/omm       +| standard public schema           | f
                 |       | =U/omm            |                                  | 
 schm1           | user1 |                   |                                  | f
 testsm          | omm   |                   |                                  | f
--More--                 |       | =U/omm            |                                  | 
 testsm1         | omm   |                   |                                  | f
 user1           | user1 |                   |                                  | f
(17 rows)
omm=# alter schema testsm owner to john ;
ALTER SCHEMA
omm=# \dn+ 
                                         List of schemas
      Name       | Owner | Access privileges |           Description            | WithBlockChain 
-----------------+-------+-------------------+----------------------------------+----------------
 blockchain      | omm   |                   | blockchain schema                | f
 cstore          | omm   |                   | reserved schema for DELTA tables | f
 db4ai           | omm   | omm=UC/omm       +| db4ai schema                     | f
                 |       | =U/omm            |                                  | 
 dbe_perf        | omm   |                   | dbe_perf schema                  | f
 dbe_pldebugger  | omm   | omm=UC/omm       +| dbe_pldebugger schema            | f
                 |       | =U/omm            |                                  | 
 dbe_pldeveloper | omm   | omm=UC/omm       +| dbe_pldeveloper schema           | f
                 |       | =U/omm            |                                  | 
 john            | john  |                   |                                  | f
                 |       | =U/omm            |                                  | 
 schm1           | user1 |                   |                                  | f
 schm2           | omm   |                   |                                  | f
 schm3           | omm   |                   |                                  | f
 pkg_service     | omm   |                   | pkg_service schema               | f
 public          | omm   | omm=UC/omm       +| standard public schema           | f
                 |       | =U/omm            |                                  | 
 testsm          | john  |                   |                                  | f
 snapshot        | omm   |                   | snapshot schema                  | f
 sqladvisor      | omm   | omm=UC/omm       +| sqladvisor schema                | f
 testsm1         | omm   |                   |                                  | f
 user1           | user1 |                   |                                  | f
(17 rows)

omm=# 

2.3 重命名testsm为testsm2

– 修改模式的名称

ALTER SCHEMA schema_name RENAME TO new_name;

mm=# alter schema testsm rename to testsm2;
ALTER SCHEMA
omm=# \dn+
                                         List of schemas
      Name       | Owner | Access privileges |           Description            | WithBlockChain 
-----------------+-------+-------------------+----------------------------------+----------------
 blockchain      | omm   |                   | blockchain schema                | f
 cstore          | omm   |                   | reserved schema for DELTA tables | f
 db4ai           | omm   | omm=UC/omm       +| db4ai schema                     | f
                 |       | =U/omm            |                                  | 
 dbe_perf        | omm   |                   | dbe_perf schema                  | f
 dbe_pldebugger  | omm   | omm=UC/omm       +| dbe_pldebugger schema            | f
                 |       | =U/omm            |                                  | 
 dbe_pldeveloper | omm   | omm=UC/omm       +| dbe_pldeveloper schema           | f
                 |       | =U/omm            |                                  | 
 john            | john  |                   |                                  | f
 pkg_service     | omm   |                   | pkg_service schema               | f
 public          | omm   | omm=UC/omm       +| standard public schema           | f
                 |       | =U/omm            |                                  | 
 schm1           | user1 |                   |                                  | f
 schm2   snapshot        | omm   |                   | snapshot schema                  | f
 sqladvisor      | omm   | omm=UC/omm       +| sqladvisor schema                | f
                 |       | =U/omm            |                                  | 
 testsm1         | omm   |                   |                                  | f
--More--         | omm   |                   |                                  | f
 schm3           | omm   |                   |                                  | f
 testsm2         | john  |                   |                                  | f
 user1           | user1 |                   |                                  | f
(17 rows)

2.4. 在模式testsm1中建表t1、插入记录和查询记录

创建表时需指定schema或切换到该schema上创建表

omm=# create table testsm1.t1(col1 char(50));
CREATE TABLE
omm=# \dt
                         List of relations
 Schema | Name  | Type  | Owner |             Storage              
--------+-------+-------+-------+----------------------------------
 public | test  | table | omm   | {orientation=row,compression=no}
 public | test1 | table | omm   | {orientation=row,compression=no}
(2 rows)

omm=# set current_schema to tes     

omm=# \dn                      
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 john            | john
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
 testsm1         | omm
 schm1           | user1
 schm2           | omm
 schm3           | omm
 testsm2         | john
 user1           | user1
(17 rows)

omm=# set current_schema to testsm1;
SET
omm=# \dt
                         List of relations
 Schema  | Name | Type  | Owner |             Storage              
---------+------+-------+-------+----------------------------------
 testsm1 | t1   | table | omm   | {orientation=row,compression=no}
(1 row)

omm=# create table t2(col1 char(10));
CREATE TABLE
omm=# \dt
                         List of relations
 Schema  | Name | Type  | Owner |             Storage              
---------+------+-------+-------+----------------------------------
 testsm1 | t1   | table | omm   | {orientation=row,compression=no}
 testsm1 | t2   | table | omm   | {orientation=row,compression=no}
(2 rows)

omm=# insert into t1 values ('test schema');
INSERT 0 1
omm=# select * from t1 ;
                        col1                        
----------------------------------------------------
 test schema                                       
(1 row)

omm=# select * from testsm1.t1;
                        col1                        
----------------------------------------------------
 test schema                                       
(1 row)

omm=# 

2.5 在会话级设置模式搜索顺序

set search_path to <模式名>

omm=# show search_path ;
omm=#  search_path 
-------------
 testsm1
(1 row)


omm=# set search_path to testsm2;
SET
omm=# show search_path ;
 search_path 
-------------
 testsm2
(1 row)

omm=# 

2.6 在数据库级设置模式搜索顺序

ALTER DATABASE <数据库> SET SEARCH_PATH TO <模式名>;

omm@modb:~$ gsql -r 
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# show search_path ;
 search_path 
-------------
 schm2
(1 row)

omm=# alter database omm  set search_path to schm3;
ALTER DATABASE
omm=# show search_path ;
 search_path 
-------------
 schm2
(1 row)

omm=# \q
omm@modb:~$ gsql -r 
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# show search_path ;
 search_path 
-------------
 schm3
(1 row)

omm=# 

2.7 .在用户级设置模式搜索顺序

ALTER USER <用户名> SET SEARCH_PATH TO <模式名>;

omm@modb:~$ gsql -d omm  -U john -W gauss@1234 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=> show search_path ;
 search_path 
-------------
 schm3
(1 row)

omm=> alter user john set search_path to testsm2;
ALTER ROLE
omm=> show search_path ;
 search_path 
-------------
 schm3
(1 row)

omm=> \q
omm@modb:~$ gsql -d omm  -U john -W gauss@1234 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=> show search_path ;
omm=>  search_path 
-------------
 testsm2
(1 row)