openGauss每日一练第8天-随堂笔记

1. 学习目标
   学习表空间与数据库对象的关系。

2. 课程学习

• 准备测试环境

su - omm
gsql -r 

--新建表空间和数据库
omm=# create tablespace joh_tbs relative location 'tablespace/joh_ts';
CREATE TABLESPACE
omm=# create database johdb1 with tablespace = joh_tbs;
CREATE DATABASE

--新建用户joh1
omm=# create user joh1 identified by 'johnny@1234';
CREATE ROLE

--赋权
omm=# alter user joh1 sysadmin;
ALTER ROLE

• 查看表空间

johdb1=> \db
          List of tablespaces
    Name    | Owner |     Location      
------------+-------+-------------------
 joh_tbs    | omm   | tablespace/joh_ts
 pg_default | omm   | 
 pg_global  | omm   | 
(3 rows)

• 创建一个新的名为ds_location1的表空间

omm=# CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'tablespace/tablespace_1';
CREATE TABLESPACE

• 使用joh1用户，访问johdb1数据库 ，在表空间ds_location1上创建表joh_t1

omm=# \c johdb1 joh1
Password for user joh1: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "johdb1" as user "joh1".
johdb1=> create table joh_t1 (col1 char(10)) tablespace ds_location1;
CREATE TABLE

• 查看johdb1数据库目前有哪些表

johdb1=> select table_catalog, table_schema, table_name, table_type from information_schema.tables where table_schema not in ('pg_catalog', 'information_schema','dbe_perf');
 table_catalog |  table_schema   | table_name | table_type 
---------------+-----------------+------------+------------
 johdb1        | db4ai           | snapshot   | BASE TABLE
 johdb1        | dbe_pldeveloper | gs_errors  | BASE TABLE
 johdb1        | dbe_pldeveloper | gs_source  | BASE TABLE
 johdb1        | public          | joh_t1     | BASE TABLE
 johdb1        | public          | my_tables  | VIEW
 johdb1        | schm4           | ttt        | BASE TABLE
 johdb1        | schm3           | ttt        | BASE TABLE
 johdb1        | schm2           | ttt        | BASE TABLE
 johdb1        | schm1           | ttt        | BASE TABLE
 johdb1        | public          | tt1        | BASE TABLE
 johdb1        | public          | joht1      | BASE TABLE
(11 rows)

• 查询表在那个表空间
  系统表在默认表空间，非系统表在指定的表空间中（否则在默认表空间）

--建表warehouse_t1指定表空间ds_location1，查看表joh_t1所在的表空间：
johdb1=> select * from pg_tables where tablename = 'joh_t1';
 schemaname | tablename | tableowner |  tablespace  | hasindexes | hasrules | hastriggers | tablecreator |            created            |         last_ddl_time         
------------+-----------+------------+--------------+------------+----------+-------------+--------------+-------------------------------+-------------------------------
 public     | joh_t1    | joh1       | ds_location1 | f          | f        | f           | joh1         | 2022-12-01 11:24:06.806058+08 | 2022-12-01 11:24:06.806058+08
(1 row)


--创建表warehouse_t12未指定表空间，则在默认表空间（不显示默认表空间名）
johdb1=> create table joh_t12 (col1 char(10));
CREATE TABLE
johdb1=> select * from pg_tables where tablename = 'joh_t12';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator |           created            |        last_ddl_time         
------------+-----------+------------+------------+------------+----------+-------------+--------------+------------------------------+------------------------------
 public     | joh_t12   | joh1       |            | f          | f        | f           | joh1         | 2022-12-01 11:27:31.91168+08 | 2022-12-01 11:27:31.91168+08
(1 row)

• 查看openGuass数据库的默认表空间

johdb1=> select datname,dattablespace,spcname from pg_database d, pg_tablespace t where d.dattablespace=t.oid;
  datname  | dattablespace |  spcname   
-----------+---------------+------------
 template1 |          1663 | pg_default
 omm       |          1663 | pg_default
 johdb1    |         16385 | joh_tbs
 template0 |          1663 | pg_default
 johdb2    |         16385 | joh_tbs
 johdb3    |         16385 | joh_tbs
 postgres  |          1663 | pg_default
(7 rows)

• 查询数据库的默认表空间上的对象

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner  
from pg_class a  
where a.relkind in ('r', 'i')  
and reltablespace='0'  
order by a.relpages desc;

                    relname                     | relkind | relpages | pg_size_pretty | reltablespace | relowner 
------------------------------------------------+---------+----------+----------------+---------------+----------
 pg_attribute                                   | r       |      184 | 1472 kB        |             0 |       10
 pg_proc                                        | r       |      140 | 1120 kB        |             0 |       10
 pg_depend                                      | r       |       59 | 472 kB         |             0 |       10
 pg_class                                       | r       |       52 | 416 kB         |             0 |       10
 pg_attribute_relid_attnam_index                | i       |       44 | 352 kB         |             0 |       10
 pg_proc_proname_all_args_nsp_index             | i       |       41 | 328 kB         |             0 |       10
 pg_proc_proname_args_nsp_index                 | i       |       39 | 312 kB         |             0 |       10
 pg_proc_proname_args_nsp_new_index             | i       |       39 | 312 kB         |             0 |       10
........

• 查询表空间ds_location1上的对像

\c musicdb user1
select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner  
from pg_class a, pg_tablespace tb  
where a.relkind in ('r', 'i')  
and a.reltablespace=tb.oid  
and tb.spcname='ds_location1'  
order by a.relpages desc;

 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
 joh_t1  | r       |        0 | 0 bytes        |         16429 |    16389
(1 row)

3. 总结
   在数据库中创建的所有的表，没有指定表空间的名字，因此都创建在数据库默认的表空间中，当我们在数据库中创建表的时候，明确指定在某个表空间创建时，这个表会存储在这个指定的表空间。
   即一个数据库中的对象，可以位于不同的表空间。