PostgreSQL事务中search_path的工作机制与多路径提交逻辑问询

先看你给出的示例代码：

-- 创建三个不同schema下的同名表
create table mlwork.namedata(
 id integer primary key,
 name varchar
);

create table mmwork.namedata(
 id integer primary key,
 name varchar
);

create table mnwork.namedata(
 id integer primary key,
 name varchar
);

BEGIN TRANSACTION;
    SET local search_path to mlwork;
    insert into namedata (id,name) values (7,'name1');
    
    SET local search_path to mmwork;
    insert into namedata (id,name) values (8,'name2');
    
    SET local search_path to mnwork;
    insert into namedata (id,name) values (9,'name3');
END TRANSACTION;  

为什么事务结束时search_path是mnwork，但三条数据没有全部插入到mnwork.namedata？核心原因在于两个关键点：

1. SET LOCAL的作用是即时生效且只影响后续语句 #

SET LOCAL search_path是事务级的临时参数设置，它会立刻改变当前会话在事务内的search_path，但只会影响这条命令之后执行的SQL语句，不会回溯修改之前已经执行的语句的行为。

2. 表名解析在语句执行时就已完成 #

PostgreSQL在执行每条INSERT语句的瞬间，会根据当时生效的search_path来解析未指定schema的表名namedata：

• 第一条INSERT执行时，search_path是mlwork，直接匹配到mlwork.namedata，这条插入操作的目标表就此固定；
• 第二条INSERT执行时，search_path已经切换为mmwork，匹配到mmwork.namedata；
• 第三条INSERT执行时，search_path是mnwork，匹配到mnwork.namedata。

这些匹配结果是在语句执行阶段就确定的，和事务后续的search_path变更完全无关，不会等到事务提交时再重新解析表名。

3. 事务提交不修改已完成的操作 #

事务提交的唯一作用是把事务内所有已经完成的修改（也就是三条分别针对不同表的插入操作）持久化到磁盘，不会对之前的操作做任何回溯调整。所以即使事务最后把search_path改成了mnwork，也不会改变前两条插入的目标表。

总结一句话：每条SQL用的是它执行那一刻的search_path，不是事务结束时的，这就是三条数据分别进入不同schema表的原因。

内容的提问来源于stack exchange，提问作者juztcode