PostgreSQL如何删除listings表中不存在于新数据馈送的旧记录

问题原因说明 #

1. 触发器报错是因为你在BEFORE UPDATE触发器中手动删除了当前操作的行，后续UPDATE操作执行时目标行已经不存在，同时会触发主键冲突异常。备份旧记录完全不需要手动操作主表数据，触发器触发时OLD变量已经存储了更新/删除前的旧值，直接插入备份表即可。
2. 缺少删除过期数据的步骤，你的现有逻辑只有Upsert（更新+新增），没有处理主表中不在新馈送里的旧行。

修正方案 #

步骤1：调整触发器函数 #

删除触发器中多余的DELETE语句，仅保留备份逻辑：

CREATE OR REPLACE FUNCTION trg_prop_backup()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO old_listings 
   VALUES (OLD.*);
   RETURN NEW;
END
$func$;

-- 触发器定义无需修改
CREATE TRIGGER prop_listings_upd_bef
BEFORE UPDATE OR DELETE ON listings
FOR EACH ROW EXECUTE FUNCTION trg_prop_backup();

步骤2：调整馈送处理逻辑，新增过期数据删除步骤 #

推荐用临时表存储本次全量馈送数据，避免重复编写馈送内容，同时提升执行效率：

-- 开启事务保证操作原子性
BEGIN;

-- 创建临时表存储本次馈送数据，事务结束自动销毁
CREATE TEMP TABLE temp_feed (
    id INT PRIMARY KEY,
    price NUMERIC,
    pub_date DATE,
    rooms INT,
    publisher VARCHAR,
    updated_date DATE
) ON COMMIT DROP;

-- 写入本次所有馈送数据
INSERT INTO temp_feed (id, price, pub_date, rooms, publisher, updated_date)
VALUES
  (1, 125, '2021-04-30', 4, 'mf', '2021-10-30'),
  (2, 200, '2021-05-03', 4, 'pmf', '2021-01-01'),
  (6, 250, '2021-04-30', 3, 'mf', '2021-10-30');

-- 执行原有Upsert逻辑
INSERT INTO listings AS p (id, price, pub_date, rooms, publisher, updated_date)
SELECT * FROM temp_feed
ON CONFLICT (id)
DO UPDATE
SET    pub_date = EXCLUDED.pub_date
     , price     = EXCLUDED.price
     , rooms     = EXCLUDED.rooms
     , updated_date = EXCLUDED.updated_date
WHERE (p.pub_date, p.price, p.rooms, p.updated_date) IS DISTINCT FROM
      (EXCLUDED.pub_date, EXCLUDED.price, EXCLUDED.rooms, EXCLUDED.updated_date);

-- 删除不在本次馈送中的过期行，删除操作会自动触发触发器备份到old_listings
DELETE FROM listings
WHERE id NOT IN (SELECT id FROM temp_feed);

-- 提交事务
COMMIT;

执行结果验证 #

执行上述逻辑后：

• listings表仅保留本次馈送的3条记录，完全符合你的预期结果
• old_listings表会存入更新前的id=1旧记录，以及被删除的id=3、4、5记录，和你期望的备份结果一致

注意事项 #

1. 确保old_listings表没有对id字段设置唯一约束/主键，因为同一个id多次更新会产生多条历史备份，需要允许id重复
2. 馈送数据量较大时，可给临时表的id字段添加索引，提升删除步骤的查询效率

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