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


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

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. 机制对比总结

特性 PostgreSQL Oracle MySQL SQL Server ClickHouse
数据结构 模式列表 单个模式 N/A 单个默认+dbo Database
默认行为 “$user”, public 与用户名相同 N/A dbo Database 名
多 schema 搜索 支持 不支持 不适用 不支持 不适用
设置方式 SET search_path ALTER SESSION N/A CREATE USER CREATE DATABASE

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 适配方案:

方案 描述 适用场景
公共同义词 创建公共同义词指向目标对象 公开的跨模式对象
私有同义词 用户创建指向其他模式对象的同义词 特定用户访问
ALTER SESSION 切换当前模式 会话级切换
直接限定 DDL 中显式使用 schema.table DDL 重写

5.2 DDL 重写规则设计

源端 Pattern 目标端 Rewrite
search_path = schema1, schema2 目标数据库创建同义词链
schema.table 限定 直接映射到目标 schema.table
$user 占位符 替换为实际用户名或默认模式
CREATE TABLE t (...) 无限定 目标端使用首个有效 schema

函数调用场景:

-- 源端 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

变更类型 影响 推荐做法
ADD COLUMN (nullable) 新字段出现在 after 使用 COALESCE
DROP COLUMN 字段消失 提前通知消费者
RENAME COLUMN drop + add 使用 expand-contract 模式
CHANGE DATA TYPE schema history 记录 使用显式 CAST

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. 多数据库兼容性 → 各数据库机制差异大,难以统一转换

这篇还发到了
今日头条 drafted
  目录