Schema：常用操作

Schema

在 PostgreSQL（PG）中，schema 是逻辑上的命名空间，用于组织数据库对象（如表、视图、函数、类型等），防止命名冲突，并可实现对象隔离。

PG schema说明
人大金仓CKA04-命令行工具KSQL 05-用户与角色 06-对象访问权限 07-库、模式、表空间

🧭 一、Schema 基础知识

• 每个数据库默认有一个 public schema。
• Schema 属于数据库内部的一个逻辑结构，不同 schema 之间的表可以重名。
• Schema 并不是数据库（Database），一个数据库可以包含多个 schema。
• search_path 决定了查询时优先使用哪个 schema 的对象。

🛠️ 二、常见 Schema 操作

✅ 创建 schema

-- 创建一个 schema，默认归当前用户所有
CREATE SCHEMA my_schema;

-- 指定拥有者
CREATE SCHEMA my_schema AUTHORIZATION some_user;

✅ 删除 schema

-- 删除 schema 和其中所有对象
DROP SCHEMA my_schema CASCADE;

-- 如果 schema 为空，才会删除
DROP SCHEMA my_schema RESTRICT;

✅ 查看所有 schema

-- 使用信息模式查询
SELECT schema_name FROM information_schema.schemata;

-- 使用系统 catalog 查询
SELECT nspname FROM pg_namespace;

✅ 修改 schema 所有者

ALTER SCHEMA my_schema OWNER TO new_owner;

✅ 重命名 schema

ALTER SCHEMA old_schema_name RENAME TO new_schema_name;

📍 三、切换默认 schema（search_path）

search_path 定义了在执行 SQL 时查找对象的优先顺序。

-- 查询当前 search_path
SHOW search_path;

-- 设置当前会话的 search_path
SET search_path TO my_schema, public;

-- 永久修改（对某个用户）
ALTER ROLE my_user SET search_path TO my_schema, public;

-- 永久修改（对整个数据库）
ALTER DATABASE mydb SET search_path TO my_schema, public;

📦 四、在 schema 中创建/访问对象

-- 创建表到指定 schema
CREATE TABLE my_schema.my_table (
  id serial PRIMARY KEY,
  name text
);

-- 查询 schema 下的表
SELECT * FROM my_schema.my_table;

🔐 五、授权 schema 权限

-- 授予使用 schema 的权限
GRANT USAGE ON SCHEMA my_schema TO some_user;

-- 授予创建对象的权限
GRANT CREATE ON SCHEMA my_schema TO some_user;

-- 撤销权限
REVOKE ALL ON SCHEMA my_schema FROM some_user;

🧪 六、查看 schema 对象

-- 查看某 schema 中的所有表
SELECT tablename FROM pg_tables WHERE schemaname = 'my_schema';

-- 查看函数
SELECT proname FROM pg_proc 
JOIN pg_namespace ON pg_proc.pronamespace = pg_namespace.oid
WHERE nspname = 'my_schema';

🎯 七、实践建议

如你提供更多使用场景（如：隔离日志表、按租户分区、审计设计等），我可以给出更贴合业务的 schema 设计方案。是否继续？

sys_stat_statements 和 sys_stat_statements_all

sys_stat_statements 和 sys_stat_statements_all 并不是人大金仓（金仓数据库 / KingbaseES）默认自带的系统表。这些更像是插件或扩展功能中生成的视图或表，用于统计 SQL 执行信息，类似于 PostgreSQL 中的 pg_stat_statements。

下面是一些背景说明和可能的来源：

🎯 一、这些表可能来自哪？

1. 扩展插件 sys_stat_statements

   • 类似 PostgreSQL 的 pg_stat_statements，人大金仓也有自己的 SQL 语句统计插件或视图系统。
   • 当启用这个插件后，会创建一些以 sys_stat_statements 开头的视图或表，用于展示 SQL 执行频率、耗时等指标。

2. 手动创建或二次开发产物

   • 在一些运维平台或二次开发系统中，可能会基于系统视图如 sys_views, sys_query_history 创建定制统计视图，命名为 sys_stat_statements_all 等。

3. DBA 工具或指标采集系统衍生

   • 有些监控系统（如金仓自己的 Kingbase Audit、第三方的 Zabbix 采集器）会自动创建统计视图用于指标归集和历史记录。

🧭 二、如何确认来源

你可以在数据库中执行以下 SQL 来确认这些对象的具体类型和来源：

-- 查看对象信息
SELECT * FROM sys_catalog.sys_class WHERE relname IN ('sys_stat_statements', 'sys_stat_statements_all');

-- 查看对象定义（如果是视图）
SELECT * FROM sys_catalog.sys_views WHERE viewname = 'sys_stat_statements';

-- 查看创建语句（如果支持）
SELECT pg_get_viewdef('sys_stat_statements', true);

🧪 三、是否默认安装？

默认情况下（比如 KingbaseES V8 或 V7 安装后）：

• 不包含 sys_stat_statements、sys_stat_statements_all。
• 需要 DBA 额外创建或通过某些组件启用。

你可以对比以下默认 schema 来确认：

SELECT nspname, relname 
FROM sys_catalog.sys_class c
JOIN sys_catalog.sys_namespace n ON c.relnamespace = n.oid
WHERE relname LIKE '%stat%';

✅ 建议

如果你希望启用 SQL 语句统计功能，建议参考以下方式：

• 开启金仓的 SQL 审计或监控扩展功能（如 sysaudit、sys_monitor 模块）。
• 自定义视图包装 sys_query_history、sys_activity 等系统视图，实现 SQL 统计功能。
• 使用 Kingbase 的官方管理工具（如 Kingbase Studio、Kingbase Control Center）来获取这些数据。

如你提供金仓的版本号（如 V8R6、V8R5）和你想统计的 SQL 信息，我可以帮你写出对应的 SQL 实现类似 pg_stat_statements 的功能。是否需要？