吐槽和记录下遇到的一些GaussDB奇葩问题

 

吐槽和记录下遇到的一些GaussDB奇葩问题

 

版本：

16:12:13 rdsAdmin@postgres > select version();
                                                          version                                                          
---------------------------------------------------------------------------------------------------------------------------
 gaussdb (GaussDB Kernel 505.0.0.SPC0100 build a99edaec) compiled at 2023-10-25 22:33:01 commit 6771 last mr 13618 release
(1 row)

 

 

问题1：create user语法中的子句“with defaultspace tablespace_name”是摆设无实际作用

具体表现为:

1.创建完后，没有数据字典视图记录相关属性

16:25:48 rdsAdmin@postgres > \x
Expanded display is on.
16:25:49 rdsAdmin@postgres > select * from pg_user where usename='root';
-[ RECORD 1 ]----+-------------
usename          | root
usesysid         | 16776
usecreatedb      | t
usesuper         | f
usecatupd        | f
userepl          | f
passwd           | ********
valbegin         | 
valuntil         | 
respool          | default_pool
parent           | 0
spacelimit       | 
useconfig        | 
nodegroup        | 
tempspacelimit   | 
spillspacelimit  | 
usemonitoradmin  | t
useoperatoradmin | f
usepolicyadmin   | f

 

2.alter user语句不存在可以修改默认表空间的子句。

16:26:55 rdsAdmin@postgres > \h alter user
Command:     ALTER USER
Description: change a database role
Syntax:
ALTER USER user_name [ [ WITH ] option [ ... ] ];
ALTER USER user_name
    RENAME TO new_name;
ALTER USER user_name [ IN DATABASE database_name ]
    SET configuration_parameter {{ TO | = } { value | DEFAULT }|FROM CURRENT};
ALTER USER user_name
    [ IN DATABASE database_name ] RESET {configuration_parameter|ALL};

where option can be:
{CREATEDB | NOCREATEDB}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {AUDITADMIN | NOAUDITADMIN}
    | {SYSADMIN | NOSYSADMIN}
    | {MONADMIN | NOMONADMIN}
    | {OPRADMIN | NOOPRADMIN}
    | {POLADMIN | NOPOLADMIN}
    | {USEFT | NOUSEFT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {INDEPENDENT | NOINDEPENDENT}
    | {VCADMIN | NOVCADMIN}
    | {PERSISTENCE | NOPERSISTENCE}
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD { 'password' [ EXPIRED ] | DISABLE | EXPIRED }
    | [ ENCRYPTED | UNENCRYPTED ] IDENTIFIED BY { 'password' [ REPLACE 'old_password' | EXPIRED ] | DISABLE }
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
    | RESOURCE POOL 'respool'
    | USER GROUP 'groupuser'
    | PERM SPACE 'spacelimit'
    | TEMP SPACE 'tmpspacelimit'
    | SPILL SPACE 'spillspacelimit'
    | NODE GROUP logic_cluster_name
    | ACCOUNT { LOCK | UNLOCK }
    | PGUSER

 

3.创建用户的时候可以瞎指定不存在的表空间。

16:28:10 rdsAdmin@postgres > \db
              List of tablespaces
    Name    |   Owner   |       Location        
------------+-----------+-----------------------
 pg_default | rdsAdmin  | 
 pg_global  | rdsAdmin  | 
 abcdef_ct  | abcdef_ct | abcdef_ct/abcdef_ct01
(3 rows)

16:28:13 rdsAdmin@postgres > create user zkm with default tablespace tbs_mygod identified by '&adighaKi@Pwd';
CREATE ROLE
Time: 53.836 ms

 

4.在云数据库 GaussDB的在线文档中，关于create role有个说明：

https://doc.hcs.huawei.com/db/zh-cn/gaussdb/24.1.30/devg-dist/gaussdb-12-0561.html

在pg里边，create user和create role几乎是一样的，GaussDB足够做参考了吧。

好吧。。浪费我时间。

压根就没往这方面去想，如果真的是无实际意义为啥不删。。

 

问题2：删除表空间报错提示问题

16:34:29 rdsAdmin@postgres > drop tablespace tbs_zkm;
ERROR:  tablespace "tbs_zkm" is not empty
Time: 303.275 ms
16:34:41 rdsAdmin@postgres > select tablespace_oid_name(decode(pc.reltablespace,0,(select dattablespace from pg_database where datname=current_database()),pc.reltablespace)) tablespace_name
gaussdb-# ,(select nspname from pg_namespace where oid=pc.relnamespace) schema_name
gaussdb-# ,pg_get_userbyid(pc.relowner) object_owner
gaussdb-# ,pc.relname object_name
gaussdb-# ,decode(pc.relkind,'r','普通表','i','索引','G','全局二级索引','S','序列','v','视图','c','复合类型','t','TOAST表','f','外表','m','物化视图','e','STREAM对象','o','CONTVIEW对象') object_type
gaussdb-# from pg_class pc
gaussdb-# where 1 =1 
gaussdb-# and cast(tablespace_name as varchar)='tbs_zkm'
gaussdb-# ------and pc.relnamespace <> (select oid from pg_namespace where nspname='pg_toast')  -- 排除TOAST相关
gaussdb-# order by pc.relkind;
 tablespace_name | schema_name | object_owner | object_name | object_type 
-----------------+-------------+--------------+-------------+-------------
(0 rows)

Time: 30.438 ms

 

但是实际上表空间tbs_zkm根据没有任何对象。

实际是因为存在数据库的默认表空间是tbs_zkm导致的。

16:37:16 rdsAdmin@postgres > \l+
                                                               List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges   |  Size  | Tablespace |                Description                 
-----------+----------+-----------+---------+-------+-----------------------+--------+------------+--------------------------------------------
 db_zkm    | rdsAdmin | UTF8      | C       | C     |                       | 23 MB  | tbs_zkm    | 
......

 

删除数据库或者修改掉库的默认表空间就可以了。