PostgreSQL循环中DELETE是否在循环结束执行?如何避免更新时空表?
结论先行
你提供的代码既存在语法错误,执行逻辑也和你想象的不一致,但调整后可以满足无空窗更新的需求。
问题1:代码存在3处语法错误
DELETE toto缺少关键字,正确写法为DELETE FROM toto- FOR循环缺少
IN关键字,正确写法为FOR _result_id IN select id from tata order by id LOOP - INSERT语句中应为复数
VALUES,不是单数value
问题2:执行逻辑说明
你问的DELETE是否会等插入完成再执行:不会,整个DO块的语句按编写顺序执行,会先执行DELETE,再执行循环插入逻辑。
但PostgreSQL默认单SQL块在同一个事务中运行,只要事务未提交,所有修改(包括DELETE和后续INSERT)都对其他连接不可见,其他查询toto表的请求在事务提交前仍然可以读到旧数据,不会出现空表。只有当整个DO块执行完成、事务成功提交后,其他请求才会同时看到更新后的全量新数据,不会出现空窗。
不过这个写法存在严重的性能问题:循环单条插入的效率极低,如果tata表数据量较大,会长时间持有表锁,可能导致其他操作超时、数据库性能下降。
更优的实现方案
方案1:单事务批量插入(适合中小数据量)
直接用INSERT INTO ... SELECT替代循环,执行效率提升数十倍,同样保证无空窗:
DO $$ BEGIN DELETE FROM toto; INSERT INTO toto (id) SELECT id FROM tata ORDER BY id; END $$;
方案2:原子表交换(适合大数据量,几乎无锁等待)
如果toto表数据量很大,推荐先把数据写入临时表再做原子交换,整个过程只有毫秒级的元数据锁,完全不会影响线上查询:
BEGIN; -- 创建和toto结构完全一致的新表 CREATE TABLE new_toto (LIKE toto INCLUDING ALL); -- 写入新数据 INSERT INTO new_toto (id) SELECT id FROM tata ORDER BY id; -- 原子替换表 ALTER TABLE toto RENAME TO old_toto; ALTER TABLE new_toto RENAME TO toto; -- 删除旧表 DROP TABLE old_toto; COMMIT;
内容的提问来源于stack exchange,提问作者Camel4488
相关产品推荐
相关产品推荐

