PostgreSQL跨连接拆分事务创建父子关系时触发外键约束违例

核心根因 #

你遇到的问题本质是PostgreSQL/EDB的事务可见性与跨会话约束检查限制，具体如下：

1. 尽管你用XA事务包裹了两个数据源的操作，但EDB为不同用户创建的独立连接属于完全分离的数据库会话。在PostgreSQL/EDB的READ COMMITTED默认隔离级别下，一个会话的事务无法读取另一个未提交会话的修改——哪怕它们属于同一个全局XA事务。
2. 延迟约束（deferrable constraint）仅在同一个数据库会话内生效：它的作用是将外键检查延迟到事务提交时，但跨会话场景下，另一个会话插入的investigation记录还处于未提交状态，当前会话的risk_factors插入操作（即使延迟检查）依然无法读取到该未提交数据，最终触发外键约束报错。
3. Oracle能正常运行是因为其事务模型支持全局XA事务内的分支会话互相可见未提交修改，这是Oracle与PostgreSQL/EDB的事务实现差异导致的。

可行解决方案 #

1. 统一操作的数据库用户

调整两个数据源的配置，使用同一个拥有两个schema操作权限的数据库用户。比如创建一个同时具备acase和apanoram对应schema读写权限的用户，将两个数据源的user属性都设置为该用户。这样所有操作会复用同一个数据库会话（或同一会话池的连接），事务内的写入彼此可见，延迟约束也能正常生效。

2. 调整应用服务器的连接共享策略

从你的数据源配置来看，使用的是支持connectionSharing属性的应用服务器（如WebSphere）。将connectionSharing设置为"Transaction"，让同一个全局XA事务内的所有操作复用同一个数据库连接（前提是两个数据源指向同一个数据库实例）。这样两个插入操作会在同一个会话内执行，跨表的外键延迟检查就能正常工作。

3. 确保外键约束为INITIALLY DEFERRED

如果已经统一了会话（方案1或2），需要确保外键约束不仅是可延迟的，还默认开启延迟检查。执行以下SQL修改约束：

ALTER TABLE risk_factors
ALTER CONSTRAINT fk_inv
DEFERRABLE INITIALLY DEFERRED;

默认的DEFERRABLE INITIALLY IMMEDIATE会在语句执行时立即检查外键，只有设置INITIALLY DEFERRED才会将检查延迟到事务提交阶段。

4. 不推荐：拆分事务（牺牲原子性）

若无法调整用户或连接配置，可先提交investigation的插入操作，再执行risk_factors的插入。但此方案会破坏事务的原子性，一旦第二步失败，第一步已提交的数据无法回滚，仅适用于非核心业务场景。

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