Elixir使用Ecto运行PostgreSQL原生查询临时表不存在如何解决

• 核心原因是PostgreSQL的临时表是会话/连接私有的：临时表仅对创建它的数据库连接可见，其他连接无法访问，连接断开后自动销毁。
• 你调用Ecto.Adapters.SQL.query时，默认每次调用都会从连接池申请不同的连接，第一条语句在连接A创建了临时表keep，后续语句拿到的是连接B/C/D，自然找不到keep表。
• 第一条返回num_rows: 0不是创建表失败：CREATE TEMP TABLE AS SELECT哪怕查询结果为空，也会成功创建表结构，只是表内没有数据，这个返回结果是合法的。
• 额外的执行顺序问题：你当前的执行顺序是建表→分析→建索引，正确顺序应该是建表→建索引→分析，否则分析操作不会统计到索引的元信息。

把所有相关操作放在同一个事务中执行，Ecto的事务会复用同一个数据库连接，保证所有语句都能访问到临时表：

SnapshotsRepo.transaction(fn ->
  create_keep = """
  CREATE TEMP TABLE "keep" AS
  SELECT min(snapshot_timestamp) AS snapshot_timestamp
  FROM   "#{camera}"
  WHERE  snapshot_timestamp <= '2018-10-31'
  GROUP  BY extract(epoch FROM snapshot_timestamp)::bigint / 600
  ORDER  BY 1;
  """
  Ecto.Adapters.SQL.query!(SnapshotsRepo, create_keep, [])

  create_index = """
  CREATE INDEX ON "keep" (snapshot_timestamp);
  """
  Ecto.Adapters.SQL.query!(SnapshotsRepo, create_index, [])

  analyze = """
  ANALYZE "keep";
  """
  Ecto.Adapters.SQL.query!(SnapshotsRepo, analyze, [])

  delete_snapshots = """
  DELETE FROM "#{camera}" a
  WHERE  snapshot_timestamp <= '2018-10-31'
  AND    NOT EXISTS (
      SELECT FROM "keep" k
      WHERE a.snapshot_timestamp = k.snapshot_timestamp
      );
  """
  Ecto.Adapters.SQL.query!(SnapshotsRepo, delete_snapshots, [])

  drop_keep = """
  DROP TABLE "keep";
  """
  Ecto.Adapters.SQL.query!(SnapshotsRepo, drop_keep, [])
end)

补充优化建议 #

如果不需要手动删除临时表，也可以在建表时加上ON COMMIT DROP参数，事务提交后自动销毁临时表，省去手动DROP的步骤：

CREATE TEMP TABLE "keep" ON COMMIT DROP AS
-- 后续查询逻辑保持不变

如果你确认对应camera表有2018-10-31之前的记录，但第一条返回num_rows为0，需要检查你的camera表名拼写、snapshot_timestamp字段的类型是否和过滤条件匹配。

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