23 模式与 search_path

PostgreSQL 中的模式是数据库内部的命名空间——一种将表、视图、函数、序列和类型归到某个名称下的方式。每个数据库至少有一个模式（public），大多数生产部署会使用多个模式。模式可以帮助你组织大型项目、在一个数据库中承载多个租户，并将扩展与应用程序代码隔离开来。理解 search_path 后，也就理解了 PostgreSQL 如何解析非限定名。

本章要点

• 创建和删除模式
• schema.table 限定名语法
• search_path 以及名称解析的工作方式
• 按角色和按数据库设置的 search_path 默认值
• 常见模式：app 模式、每个租户一个模式、将扩展分离出来

示例

-- Create a schema and a table inside it
CREATE SCHEMA IF NOT EXISTS app;

DROP TABLE IF EXISTS app.events_example;
CREATE TABLE app.events_example (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind text NOT NULL,
  at   timestamptz NOT NULL DEFAULT current_timestamp
);

INSERT INTO app.events_example (kind) VALUES ('login'), ('logout');

-- Fully qualified vs. unqualified access
SELECT * FROM app.events_example ORDER BY id;

-- Make the schema findable without prefixing every name
SET search_path TO app, public;
SELECT * FROM events_example ORDER BY id;   -- resolved as app.events_example

-- Inspect the current search path
SHOW search_path;

-- List schemas and the tables in each
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename
LIMIT 10;

-- Reset to default
SET search_path TO public;

当你写下 SELECT * FROM events 时，PostgreSQL 会按照 search_path 中列出的模式顺序，在每个模式中查找这个名称，并使用第一个匹配项。默认的 search_path 是 "$user", public——意思是“先查找一个以连接用户命名的模式，再查找 public”。如果存在一个与用户同名的模式，它就会优先匹配。

你可以按会话（SET search_path TO ...）、按角色（ALTER ROLE alice SET search_path TO ...）或按数据库（ALTER DATABASE app_db SET search_path TO ...）修改 search_path。后两种设置会在重新连接后继续生效。

模式隔离是将扩展对象（postgis、pg_trgm）放在 public 之外、为每个租户提供自己的命名空间，或按逻辑组织 audit.*、app.*、staging.* 表的标准方式。

运行示例

运行方式：

$ psql -f source/schemas.sql postgres
CREATE SCHEMA
DROP TABLE
CREATE TABLE
INSERT 0 2
 id |  kind  |              at
----+--------+-------------------------------
  1 | login  | 2024-xx-xx xx:xx:xx.xxxxxx+00
  2 | logout | 2024-xx-xx xx:xx:xx.xxxxxx+00
(2 rows)
 ...
 search_path
-------------
 app, public
(1 row)
 ...

常见陷阱

• 在函数中忘记设置 search_path：SECURITY DEFINER 函数使用其所有者的权限，但如果没有函数级设置，它使用的是调用者的 search_path。请将路径限制为只包含受信任的模式，并把 pg_temp 放在最后（例如，在 app 受信任时，使用 SET search_path = pg_catalog, app, pg_temp），同时为敏感对象名称加上限定名。
• 把扩展放进 public：扩展会将对象安装到当前所在的模式中。许多团队会执行 CREATE SCHEMA extensions; ALTER ROLE ... SET search_path = "$user", public, extensions;，并在那里创建扩展。
• 删除存在依赖关系的模式会失败。使用 DROP SCHEMA name CASCADE 删除模式及其依赖对象。这次删除可以在事务中回滚；提交之后，恢复就需要备份或重新创建。
• 别忘了 \dt 只显示当前 search_path 模式中的表。使用 \dt *.* 查看全部内容。

下载

SQL 文件：schemas.sql · 全部示例文件 · 准备工作