PG迁移必读(八)：深度剖析PG的Search_Path
Barry Chen
​

背景与挑战

在从 Sybase 迁移到 PostgreSQL 的过程中，我们遇到了一个特殊的挑战。在原始的 Sybase 系统中，多个医院的数据库共享一套存储过程代码，这些医院数据库之间存在存储过程的相互调用。Sybase 通过跨数据库调用功能实现这一点，但在 PostgreSQL 中，这种跨数据库调用不被支持。因此，我们采用为每个医院创建 Schema 的方式来模拟原有的跨数据库调用功能。然而，这种方法带来了潜在的问题，需要我们警惕。

问题重现

假设有一个病人转院的场景，A 医院的 transferOut 存储过程需要调用 B 医院的 transferIn 存储过程，将病人信息存入到B医院数据库表中。在 Sybase 中，是直接通过跨服调用B存储过程完成的。但在 PostgreSQL 中，们发现调用结果与预期不符。具体来说，即使 transferIn 存储过程是在 B 医院的 Schema 下执行的，插入的病人信息却出现在 A 医院的表中！

Syabse源码

在Sybase中，transferOut和transferIn伪代码分别如下：

procedure transferOut( destinationHospital varchar, patientName varchar )

        execute destinationHospital +'..transferIn('+ patientName + ')' -- 这里destinationHospital 是医院数据库名称，跨库调用transferIn

procedure transferIn( patientName varchar )        

        insert into patient (name) values( patientName )  -- 将patient信息插入当前数据库表中。

当从A医院转入B医院时，在Sybase中运行SQL

A..transferOut( 'B', '张三' )

后，B医院数据库中就会有一条张三的记录了。

转换为PG后的源码

现在将上面的代码转为PostgreSQL, 仅仅将 destinationHospital 从Sybase的Database替换为PG的schema，伪代码如下：

procedure transferOut( destinationHospital varchar, patientName varchar )

          execute destinationHospital+'.transferIn('+ patientName + ')'       -- 这里destinationHospital是医院schema名称，跨Schema调用transerIn

-- tranferIn保持不变

procedure transferIn( patientName varchar )        

        insert into patient (name) values( patientName )  

在PG中运行SQL（假设当前Connection指定了schema是A）：

A.transferOut( 'B', '张三' )

你会神奇地发现，B医院的表中并没有张三的记录，张三的记录最终会被插入到A医院的patient表中！

原因分析

这个问题的原因在于 PostgreSQL 的 search_path 参数。search_path 控制了 PostgreSQL 在解析 SQL 查询时搜索表、视图、索引和其他数据库对象的顺序。当存储过程在 Schema B 下运行时，如果没有指定 Schema，PostgreSQL 会根据 search_path 中定义的顺序在不同的 Schema 中搜索这些对象。

 

回到上述问题，因为当前Connection指定了schema是A，search_path就被设置为A，PostgreSQL 只会从A中搜索表，导致数据最终被写入到A的数据库表中。

Search_Path 介绍： 

search_path 的作用主要有三个：

  1. 避免名称冲突： 在大型数据库中，多个 Schema 可能包含同名的表或函数。search_path 允许开发者控制查询时的搜索顺序，从而避免名称冲突。

  2. 提高查询效率： 通过将常用的 Schema 放在 search_path 的前面，可以减少数据库在解析查询时的搜索范围，从而提高查询效率。

  3. 安全性： search_path 可以限制对某些 Schema 的访问，增加数据库的安全性。

     

search_path 可以通过三种方式进行设置：

  1. 系统级别： 在 postgresql.conf 文件中设置全局的 search_path，这将作为所有会话和用户的默认值。

    search_path = 'schema1,schema2'

  2. 用户级别： 使用 ALTER ROLE 命令设置用户的默认 search_path。

    ALTER ROLE username SET search_path TO schema1, schema2;

  3. 会话级别： 使用 SET 命令临时设置当前会话的 search_path。

    SET search_path TO schema1, schema2;

解决方案

针对上述从 Sybase 迁移到 PostgreSQL 的问题，我们有两种解决方案：

  1. 方案一： 为每个医院建立一个版本，在各自的版本源码里，引用对象时都加上当前医院的 Schema。这种方法安全，但维护量大。

  2. 方案二： 维持一个版本，在源码里根据医院参数动态调整 search_path。这种方法维护工作量小，但不安全，风险大。这是由于在动态调整search_path过程中，如果出现异常，没有正确恢复search_path设置，会导致后续SQL错误定位对象。

考虑到安全性，我们最终向客户推荐了方案一，即将 Schema 写入到存储过程中，维护多个医院的版本源码。