search_path如何影响标识符解析和“当前模式”？

Question

是否可以定义默认在哪个模式中创建新表？ （由“不合格的表名”引用。）

我已经看到了有关在 Postgres 中使用“搜索路径”的一些详细信息，但我认为它只在检索数据时起作用，而不是在创建数据时起作用。

我有一堆 SQL 脚本，它们创建了许多表。我不想修改脚本，而是想默认设置数据库在特定模式中创建表 - 当它们具有不合格的名称时。

这可能吗？

Answer 1

什么是架构搜索路径 search_path？

手册：

[...] 表通常由非限定名称引用，其中包括
只是表名。系统确定哪个表是指
遵循搜索路径，该路径是要查找的架构列表。

大胆强调我的。这解释了标识符解析。

“当前架构”（或“默认架构”）是每个文档：

搜索路径中命名的第一个架构称为当前架构
架构。除了是第一个搜索模式之外，它也是
如果使用 CREATE TABLE 命令，将在其中创建新表的架构
未指定架构名称。

大胆强调我的。系统架构 pg_temp （当前会话临时对象的架构）和 pg_catalog 自动成为搜索的一部分路径并按此顺序首先进行搜索。 手册：

pg_catalog 始终是搜索路径的有效部分。如果不是
在路径中显式命名，然后之前隐式搜索它
搜索路径的模式。这确保内置名称将
总是可以找到的。但是，您可以显式地将 pg_catalog 放置在
如果您希望使用用户定义的名称，则位于搜索路径的末尾
覆盖内置名称。

按照原文加粗强调。并且 pg_temp 位于此之前，除非将其放在不同的位置。

怎么设置呢？

有多种方法可以设置运行时变量 search_path - 或任何运行时变量。

1. 在配置文件中为所有数据库中的所有角色设置集群范围的默认值postgresql.conf （并重新加载）。小心一点！

    search_path = 'blarg,public'
   

   此设置的出厂默认值< /a> 是：

    search_path = "$user",public
   

   注意：

   
   <块引用>

   第一个元素指定与
   要搜索当前用户。如果不存在这样的架构，则忽略该条目。

   还有：

   <块引用>

   如果列表项之一是特殊名称 $user，则架构
   如果存在，则替换为 CURRENT_USER 返回的名称
   这样的模式，并且用户拥有它的 USAGE 权限。 （如果不，
   $user 被忽略。）

   
   

2. 使用 ALTER SYSTEM 从 Postgres 内部执行相同操作（Postgres 12 或更高版本）。 手册：

ALTER SYSTEM 将给定的参数设置写入
postgresql.auto.conf 文件，除了读取
postgresql.conf.

3. 将其设置为一个数据库的默认值：

    ALTER DATABASE test SET search_path = blarg,public;
   

4. 将其设置为您连接的角色的默认值（集群范围内有效）：

    ALTER ROLE foo SET search_path = blarg,public;
   

5. 甚至（通常是最好的！）作为数据库中的角色的默认值：

    ALTER ROLE foo IN DATABASE test SET search_path = blarg,public;
   

6. 在脚本顶部编写命令。或者在您的数据库会话中执行它：

    SET search_path = blarg,public;
   

7. 为函数范围设置特定的search_path（以防止恶意用户使用足够的权限）。了解编写SECURITY DEFINER手册中的安全功能。

   创建函数 foo()
     退货无效
     语言 plpgsql 安全定义器 SET search_path=blarg,public,pg_temp
     作为
   $函数$
   开始
      ——做事
   结尾
   $func$;

列表中较高的数字胜过较低的数字。
手册有更多的方法，例如设置环境变量或使用命令行选项。

如何检查当前设置？

检查配置文件 postgresql.conf（和 postgresql.auto.conf）。或者（更方便）使用系统视图 pg_file_settings< /code>，它总结了所有配置文件的内容：

SELECT * FROM pg_file_settings WHERE name = 'search_path';

要查看会话中的当前设置：

SHOW search_path;

或从系统视图中获取完整信息pg_settings：

SELECT * FROM pg_settings WHERE name = 'search_path';

每个角色/数据库的设置存储在系统目录中 pg_db_role_setting（默认情况下无）。检查：

SELECT d.datname AS database, setrole::regrole AS role, s.setconfig
FROM   pg_db_role_setting s
JOIN   pg_database d ON d.oid = s.setdatabase
WHERE  s.setconfig::text ~ 'search_path';

如何重置？

要重置：

RESET search_path;

重置： postgresql.org/docs/current/sql-reset.html" rel="nofollow noreferrer">手册：

默认值定义为参数将要使用的值
如果在当前会话中没有为其发出SET，则已经有过。

如何知道这将重置为什么？

SELECT reset_val FROM pg_settings WHERE name = 'search_path';

reset_val 文本
RESET 将在当前会话中将参数重置为的值

或在更改之前保存当前状态（可能与重置值不同）首先，SET 将其恢复到该状态（而不是RESET）。有一个方便的函数 set_config() 接受一个变量，而不是SET。示例 PL/pgSQL 代码块：

DO
$do$
DECLARE
   _org_search_path text := current_setting('search_path');
BEGIN
   SET search_path = public, pg_temp;
   -- do stuff
   PERFORM set_config('search_path', _org_search_path, true);
END;
$do$

或使用 设置本地。这会持续到当前事务结束，这可能会超过函数的结束。细微差别！

Answer 2

搜索路径确实是你想要的：

% create schema blarg;
% set search_path to blarg;
% create table foo (id int);
% \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 blarg  | foo  | table | pgsql