多数据库模式命名空间问题

1. 问题背景

PostgreSQL 的 search_path 可以设置多个 schema，但其他数据库如 Oracle 的 ALTER SESSION SET CURRENT_SCHEMA = ZXF 只能设置一个。这在一条 SQL 包含多个 schema 的场景下，无法进行直接转换。

典型问题场景

当前用户：test_zxf_20250811

设置 search_path:
SET search_path TO "$user", public, zxf;

SELECT current_setting('search_path');
-- 结果: "$user", public, zxf

-- 创建函数（使用了显式 schema 前缀 zxf）
CREATE OR REPLACE FUNCTION zxf.is_valid_email(p_email text)
RETURNS boolean
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    RETURN p_email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
END;
$$;

-- 创建表（未使用显式 schema 前缀）
CREATE TABLE zxf_user_info
(
    id          bigserial PRIMARY KEY,
    username    varchar(50) NOT NULL,
    email       varchar(200) NOT NULL,

    CONSTRAINT ck_user_info_email
        CHECK (is_valid_email(email))  -- 函数调用依赖 search_path 解析
);

-- 期望逻辑复制发布的 DDL：
CREATE TABLE test_zxf_20250811.zxf_user_info
(
    id          bigserial PRIMARY KEY,
    username    varchar(50) NOT NULL,
    email       varchar(200) NOT NULL,

    CONSTRAINT ck_user_info_email
        CHECK (zxf.is_valid_email(email))  -- 目标端需显式限定
);

2. 主流数据库 Schema 机制对比

2.1 PostgreSQL search_path

核心机制：search_path 是一个有序列表，指定了未限定对象名的搜索顺序。

-- 默认值
search_path = "$user", public

解析流程（以 SELECT * FROM mytable 为例）：

1. 按 search_path 中的模式顺序依次搜索
2. 在每个模式中查找名为 mytable 的表
3. 先到先得原则：找到第一个匹配即停止搜索
4. 若均未找到，返回错误

“$user” 的特殊含义：

• 表示当前会话的 CURRENT_USER 对应的模式
• 如果不存在与 current_user 同名的模式，该条目被静默忽略

实际解析顺序：

pg_temp (如果存在) → pg_catalog → search_path 中的显式列表

search_path 影响的对象类型：

• 表、视图、序列、触发器、域类型
• 函数/运算符（temp schema 不会搜索函数名，安全考虑）

2.2 Oracle CURRENT_SCHEMA

设计哲学：用户与模式一对一绑定

-- 创建用户时自动创建同名模式
CREATE USER app_owner IDENTIFIED BY password;
-- 模式 app_owner 自动创建

解析顺序：

当前模式 → 私有同义词 → 公有同义词 → SYS 字典

为什么只能设置单个 schema：

1. 编译时绑定：PL/SQL 代码在编译时就确定了对象解析
2. 同义词机制替代：通过公共同义词实现跨模式访问
3. 安全模型：基于模式的权限管理更清晰

2.3 MySQL

架构：SCHEMA 和 DATABASE 是同义词，不存在 SQL 标准意义上的 schema 概念。

-- 这两者等价
CREATE SCHEMA mydb;
CREATE DATABASE mydb;

局限性：

• 无真正隔离的 schema
• 不同 schema 下的表不能在单条 SQL 中直接 JOIN
• 权限模型基于数据库级别

2.4 SQL Server

架构：采用 user-schema 分离模型

-- 创建模式
CREATE SCHEMA Finance;

-- 创建用户并指定默认模式
CREATE USER Wanida FOR LOGIN WanidaBenshoof
WITH DEFAULT_SCHEMA = Finance;

解析顺序（对象名未限定时）：

1. 用户默认模式
2. dbo 模式
3. 报错

关键特性：

• 模式可以独立于用户存在
• 一个主体可以拥有多个模式

2.5 ClickHouse

架构：Database 作为 namespace

CREATE DATABASE analytics;
CREATE TABLE analytics.events (...) ENGINE = MergeTree();

与 PostgreSQL 的差异：

• ClickHouse 不支持多级 schema（database.table.column 是两层）
• PostgreSQL 是 database → schema → table（三层）

3. 机制对比总结

4. 逻辑复制中的问题

4.1 PostgreSQL 原生逻辑复制的限制

“The database schema and DDL commands are not replicated.”
— PostgreSQL Documentation

关键问题：

• 复制工作者使用受限的 search_path：

  -- PostgreSQL 逻辑复制 worker 执行时
  -- search_path 被设置为仅 "pg_catalog"（安全原因）

• 任何依赖 search_path 的函数调用在 replication worker 中可能解析到错误的模式

4.2 函数解析风险

-- 如果函数定义依赖 search_path
CREATE FUNCTION inner_func() ...;  -- 无模式限定

在 replication worker 中可能解析到错误的模式中的函数。

解决方案：

-- 在 SECURITY DEFINER 函数中显式设置 search_path
CREATE FUNCTION safe_function()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = my_safe_schema, pg_catalog;

5. 跨数据库迁移的 Schema 处理策略

5.1 源端多 schema search_path 到目标端的适配

问题场景：

-- 源端 PostgreSQL
SET search_path TO app_v1, app_v2, public;
SELECT * FROM my_func();  -- 在 app_v1 中找到

目标端 Oracle 适配方案：

5.2 DDL 重写规则设计

函数调用场景：

-- 源端 PostgreSQL
SET search_path TO utils, public;
SELECT my_function(1, 2);  -- 解析到 utils.my_function 或 public.my_function

-- 目标端 Oracle：使用包装函数
CREATE OR REPLACE FUNCTION my_function(p1 NUMBER, p2 NUMBER)
RETURN NUMBER AS
BEGIN
    RETURN target_schema.my_function_impl(p1, p2);
END;
/

5.3 CDC 场景下的 Schema Evolution

6. 现有开源项目处理方式

6.1 Debezium

PostgreSQL 特性：

• 使用 pgoutput 或 decoderbufs 插件
• 不直接支持 DDL 复制

Schema 处理：

# Kafka Topic 命名包含 schema
topic = {database.server.name}.{schema}.{table}

# 事件消息结构
{
  "schema": {...},
  "payload": {
    "source": {
      "schema": "app_schema",  // 显式包含
      "table": "orders"
    }
  }
}

6.2 Maxwell（MySQL 专用）

• 启动时捕获完整 schema
• 将 schema 存储在自己的数据库中
• DDL 变更通过 output_ddl 选项输出到 Kafka topic

6.3 Oracle DDL 复制方案

logical_ddl 扩展（PGXN）：

1. 使用 event trigger 捕获 DDL
2. 将 DDL 解析为 JSON 格式
3. 通过逻辑复制传递到订阅端

自定义 DDL 捕获触发器：

CREATE OR REPLACE FUNCTION log_ddl_changes()
RETURNS event_trigger AS $$
BEGIN
    INSERT INTO ddl_log (object_tag, ddl_command, timestamp)
    VALUES (tg_tag, current_query(), current_timestamp);
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER log_ddl_trigger
ON ddl_command_end
EXECUTE FUNCTION log_ddl_changes();

7. PostgreSQL 官方 DDL 复制草案

PostgreSQL 社区正在开发 DDL 复制方案，核心思路：

将 DDL 解析为 JSONB 格式（而非纯文本 + search_path）

JSON 格式优势：

1. 可被订阅端任意转换
2. 支持 schema A → schema B 的映射
3. 不依赖运行时的 search_path 设置
4. 可被机器编辑处理

8. 安全建议

-- 生产环境：移除 public 的 CREATE 权限
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 应用程序用户：只搜索自己的 schema
ALTER ROLE app_user SET search_path TO "$user";

-- SECURITY DEFINER 函数：显式设置安全的 search_path
CREATE FUNCTION safe_func() SET search_path = my_schema, pg_catalog;

9. 核心挑战总结

1. search_path 的隐式解析 → 需要显式限定或同义词链
2. DDL 复制 → PostgreSQL 原生不支持，依赖外部工具
3. 函数依赖 search_path → 在目标端显式设置
4. schema 映射 → 需要配置转换规则
5. 多数据库兼容性 → 各数据库机制差异大，难以统一转换