pgbouncer事务池模式下Rails中PostgreSQL临时表的持久化安全性问题

核心结论 #

第二次执行插入操作时，临时表几乎肯定不存在，会触发"表不存在"的数据库错误。

原因分析 #

• pgbouncer的pool_mode = transaction是按事务分配连接：每个事务完成后，数据库连接会被立即放回连接池，供其他请求或后台作业复用。
• 你第一次执行MyModel.connection.execute 'create temp table ...'时，默认会自动提交一个独立事务，事务结束后连接就被pgbouncer回收了。
• 间隔1分钟后第二次执行插入操作，Active Record会从pgbouncer池里获取任意空闲连接——这个连接和第一次创建临时表的连接不是同一个，自然没有tmp_test这个临时表。
• 补充：PostgreSQL临时表的生命周期完全和数据库连接绑定，只有在创建它的连接上才能访问，连接被关闭或回收复用后，临时表会被自动销毁。

可行解决方案 #

1. 同一事务内完成所有操作 #

如果两次操作属于同一个业务逻辑，用Active Record事务块包裹，确保全程使用同一个连接：

MyModel.transaction do
  # 创建临时表
  MyModel.connection.execute 'CREATE TEMP TABLE tmp_test (id INT, name TEXT);'
  # 这里可以加入延迟或其他业务逻辑
  sleep(60) # 模拟1分钟间隔
  # 插入数据
  MyModel.connection.execute "INSERT INTO tmp_test VALUES (1, 'test');"
end

注意：事务结束后连接会被回收，临时表也会随之销毁，无法跨事务访问。

2. 改用普通表加标识区分数据 #

如果需要跨请求/作业存储临时数据，放弃临时表，改用普通表，用请求ID、Sidekiq作业ID作为唯一标识来隔离数据：

# 提前创建普通表
# CREATE TABLE tmp_test (job_id VARCHAR(255), id INT, name TEXT);

# 在Sidekiq作业中
job_id = self.job_id
MyModel.connection.execute "INSERT INTO tmp_test (job_id, id, name) VALUES ('#{job_id}', 1, 'test');"
# 后续操作通过job_id过滤数据
MyModel.connection.execute "SELECT * FROM tmp_test WHERE job_id = '#{job_id}';"
# 作业完成后清理数据
MyModel.connection.execute "DELETE FROM tmp_test WHERE job_id = '#{job_id}';"

3. 调整pgbouncer连接模式（不推荐） #

把pgbouncer的pool_mode改为session模式，这样每个请求/作业会绑定一个连接直到会话结束。但这种模式会大幅降低连接池的复用效率，可能导致数据库连接数飙升，仅适合低并发场景。

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