PostgreSQL search_path 的工作原理与宣传的结果不一致

在本文中，我们将介绍 PostgreSQL 数据库中 search_path 参数的工作原理以及与宣传之间的差距。search_path 参数是 PostgreSQL 数据库中的一个重要参数，用于指定在引用数据库对象时系统应该搜索的schema顺序。然而，通过实际测试和观察，我们发现了一些不符合预期的结果。

阅读更多：PostgreSQL 教程

search_path 的介绍

在 PostgreSQL 中，当查询引用数据库对象（如表、函数和视图）时，系统需要知道在哪个 schema 中查找这些对象。如果未指定 schema，则系统将按照特定的搜索顺序在多个 schema 中依次查找。这个搜索顺序由 search_path 参数控制。search_path 参数是一个以逗号分隔的 schema 列表，系统按照列表的顺序依次查找每个 schema。

下面是一个示例：

-- 创建两个 schema，并在其中分别创建相同名称的表
CREATE SCHEMA schema1;
CREATE SCHEMA schema2;

CREATE TABLE schema1.my_table (id INT);
CREATE TABLE schema2.my_table (id INT);

在上面的示例中，我们创建了两个 schema：schema1 和 schema2，并在每个 schema 中创建了一个名为 my_table 的表。在查询时，如果没有指定 schema，则系统会按照 search_path 参数中指定的顺序依次查找。

search_path 的问题

根据 PostgreSQL 官方文档的描述，修改 search_path 参数应该是一个 session 级别的设置，会对当前会话的所有查询中产生影响。然而，通过实际测试和观察，我们发现在某些情况下，search_path 参数的修改并没有按照预期起作用。

例如，假设我们将当前会话的 search_path 参数改为 schema2,schema1，然后执行以下查询：

-- 查询 my_table
SELECT * FROM my_table;

根据预期，系统应该先在 schema2 中查找 my_table，然后再在 schema1 中查找。然而，实际上系统依然首先在 schema1 中查找，而不是按照修改后的 search_path 参数进行查找。经过进一步的测试，我们发现无论如何修改 search_path 参数的值，系统始终按照某种默认的顺序进行查找。

示例说明

为了更清楚地说明这个问题，我们在以下示例中创建了三个 schema：schema1、schema2 和 schema3，并在每个 schema 中创建了一个名为 my_table 的表。

CREATE SCHEMA schema1;
CREATE SCHEMA schema2;
CREATE SCHEMA schema3;

CREATE TABLE schema1.my_table (id INT);
CREATE TABLE schema2.my_table (id INT);
CREATE TABLE schema3.my_table (id INT);

然后，我们将 search_path 参数修改为 schema3,schema2,schema1，并在查询时不带任何 schema 前缀：

-- 查询 my_table
SELECT * FROM my_table;

根据预期，系统应该首先在 schema3 中查找 my_table，然后再在 schema2 中查找，最后在 schema1 中查找。然而，我们观察到系统实际上是先在 schema2 中查找，然后再在 schema3 中查找，最后在 schema1 中查找。这与官方文档所述的不符。

总结

在本文中，我们介绍了 PostgreSQL 数据库中 search_path 参数的工作原理，并观察了它与宣传之间的差距。尽管官方文档宣称修改 search_path 参数会影响会话中所有查询的 schema 查找顺序，但通过实际测试和观察，我们发现系统并没有按照预期的顺序进行查找。

这个问题可能会对开发人员和数据库管理员带来困惑和不便。在使用 search_path 参数时，我们建议小心并确保对查询中引用的对象指定明确的 schema 前缀，以避免不符合预期的结果。此外，我们也希望 PostgreSQL 官方能够对这个问题进行修复和改进，以提供更一致和可预测的搜索机制。