合 PG中避免大事务的分批更新、分批插入、分批删除、分批提交等
Tags： PGPostgreSQL分批删除分批提交分批插入分批更新
👉 本文共约102个字，系统预计阅读时间或需1分钟。
环境
1 2 3 4 5 | CREATE TABLE t AS SELECT i, i % 5 AS j FROM generate_series(1, 100000) AS t(i); create table t1 as select * from t where 1=2; |
分批UPDATE
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | DO $$ DECLARE counter INTEGER := 0; rows RECORD; BEGIN FOR rows IN (SELECT ctid FROM t) LOOP update t set i=i+100 where ctid=rows.ctid; counter := counter + 1; IF counter % 10000 = 0 THEN COMMIT; END IF; END LOOP; IF counter % 10000 <> 0 THEN COMMIT; END IF; END $$; |
分批DELETE
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | DO $$ DECLARE counter INTEGER := 0; rows RECORD; BEGIN FOR rows IN (SELECT ctid FROM t) LOOP delete from t where ctid=rows.ctid; counter := counter + 1; IF counter % 10000 = 0 THEN COMMIT; END IF; END LOOP; IF counter % 10000 <> 0 THEN COMMIT; END IF; END $$; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
