主页 / dba / 问题 / 130421
Accepted
J-16 SDiZ
Asked: 2016-02-26 01:44:37 +0800 CST

用于 pl/pgsql 的 TEMP 表替代方案

  • 772

我正在尝试MERGE使用 pl/pgsql 来模拟行为：

-- Generate the data from funtion
CREATE TEMP TABLE temp_x  (id int, id2 int, data text, created_at timestamp, updated_at timestamp)  ON COMMIT DROP;
INSERT INTO temp_x SELECT * FROM set_gernating_function(p);

-- DELETE record with same id2
DELETE FROM x WHERE NOT EXISTS (SELECT 1 FROM temp_x WHERE temp_x.id=x.id) AND id2=p.id2;

-- UPSERT by (id, id2)
INSERT INTO x
    SELECT * FROM temp_x z
  ON CONFLICT(id, id2) DO UPDATE
    SET
      updated_at=excluded.updated_at,
      data=excluded.data;

但是临时表的使用会非常pg_class快地产生膨胀和pg_attr表——然后我可以更快地做真空——并影响其他查询。任何想法？

有一些限制：

  1. created_at如果 id 已经存在，则保留时间
  2. x 有一个删除触发器，所以没有不必要的删除。
  3. 速度set_generating_function很慢
  4. temp_x 的大小很小。(< 50)
  5. 运行速度非常高（每秒千次）
postgresql performance

1 个回答

  1. Best Answer

    您不需要临时表：

    您可以在单个语句中执行此操作：

    with temp_x (id, id2, data, created_at, updated_at) AS (
   SELECT * 
   FROM set_gernating_function(p)
), deleted as (
  DELETE FROM x 
  WHERE NOT EXISTS (SELECT 1 
                    FROM temp_x 
                    WHERE temp_x.id = x.id) 
    AND id2=p.id2
)
-- UPSERT by (id, id2)
INSERT INTO x
SELECT * 
FROM temp_x z
  ON CONFLICT(id, id2) DO UPDATE
    SET updated_at = excluded.updated_at,
        data = excluded.data;
    • 3

相关问题