GBase8c 系统命令\d tbname查看表结构异常处理

场景： 在数据库终端执行 \d tablename 报错 ERROR: column pol.policywithchkqual does not exist

解决方法：

1、查看系统参数

select * from pg_settings where name like '%system%';

2、查看allow_system_table_mods的值是否为on。如果为off，则修改参数，并重启

[gbase@keep-gbase8c-primary ~]$ gsql -p 15400 -d postgres -r
# allow_system_table_mods 修改此参数存在风险，视图修复后，还原参数
postgres=# alter system set allow_system_table_mods = 1;

[gbase@keep-gbase8c-primary ~]$ gs_om -t restart

3、删除系统视图

[gbase@keep-gbase8c-primary ~]$ gsql -p 15400 -d postgres -r
postgres=# drop view pg_catalog.pg_rlspolicies;

4、重建视图

create view pg_catalog.pg_rlspolicies as SELECT n.nspname AS schemaname, c.relname AS tablename,
    pol.polname AS policyname,
        CASE
            WHEN pol.polpermissive THEN 'PERMISSIVE'::text
            ELSE 'RESTRICTIVE'::text
        END AS policypermissive,
        CASE
            WHEN pol.polroles = '{0}'::oid[] THEN string_to_array('public'::text, ' '::text)::name[]
            ELSE ARRAY( SELECT pg_authid.rolname
               FROM pg_authid
              WHERE pg_authid.oid = ANY (pol.polroles)
              ORDER BY pg_authid.rolname NULLS FIRST)
        END AS policyroles,
        CASE pol.polcmd
            WHEN 'r'::"char" THEN 'SELECT'::text
            WHEN 'a'::"char" THEN 'INSERT'::text
            WHEN 'w'::"char" THEN 'UPDATE'::text
            WHEN 'd'::"char" THEN 'DELETE'::text
            WHEN '*'::"char" THEN 'ALL'::text
            ELSE NULL::text
        END AS policycmd,
    pg_get_expr(pol.polqual, pol.polrelid) AS policyqual,
    pg_get_expr(pol.withchkqual, pol.polrelid) AS policywithchkqual,
    pol.secrelevantcols, pol.enable
   FROM pg_rlspolicy pol
   JOIN pg_class c ON c.oid = pol.polrelid
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relowner = (( SELECT pg_authid.oid
   FROM pg_authid
  WHERE pg_authid.rolname = "current_user"())) OR ( SELECT pg_authid.rolsystemadmin
   FROM pg_authid
  WHERE pg_authid.rolname = "current_user"());

创建成功后，测试 \d tbname参数，无报错

5、修改数据库参数，并重启

[gbase@keep-gbase8c-primary ~]$ gsql -p 15400 -d postgres -r
postgres=# alter system set allow_system_table_mods = 0 ;

[gbase@keep-gbase8c-primary ~]$ gs_om -t restart