在PostgreSQL中实现update | delete limit

背景

使用MySQL的用户可能会比较熟悉这样的用法，更新或删除时可以指定限制更新或删除多少条记录。

达到限定的记录数后不再继续进行下去，而是返回。

delete from tbl where xxx limit 100;

update tbl set xxx=xxx where xxx limit 100;

目前PostgreSQL没有类似的语法，但是可以通过其他手段来达到同样的效果。

with语法实现

创建测试表

postgres=# create table t(id int primary key, info text);
CREATE TABLE
postgres=# insert into t select generate_series(1,1000000);
INSERT 0 1000000

update | delete limit 用法如下

postgres=# with t1 as (select id from t where id between 1 and 1000 limit 10) 
                  delete from t where id in (select * from t1);
DELETE 10

postgres=# with t1 as (select id from t where id between 1 and 1000 limit 10) 
                   update t set info='new' where id in (select * from t1);
UPDATE 10

postgres=# explain with t1 as (select id from t where id between 1 and 1000 limit 10) 
postgres-#                    update t set info='new' where id in (select * from t1);
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Update on t  (cost=4.89..12.93 rows=1 width=38)
   CTE t1
     ->  Limit  (cost=0.42..4.44 rows=1 width=4)
           ->  Index Only Scan using t_pkey on t t_1  (cost=0.42..4.44 rows=1 width=4)
                 Index Cond: ((id >= 1) AND (id <= 1000))
   ->  Nested Loop  (cost=0.45..8.48 rows=1 width=38)
         ->  HashAggregate  (cost=0.02..0.03 rows=1 width=32)
               Group Key: t1.id
               ->  CTE Scan on t1  (cost=0.00..0.02 rows=1 width=32)
         ->  Index Scan using t_pkey on t  (cost=0.42..8.44 rows=1 width=10)
               Index Cond: (id = t1.id)
(11 rows)

使用with模拟必须有PK或者非空UK，否则需要用行号，但是行号来扫就慢了，没走tid scan方法。

postgres=#  with t1 as (select ctid from t where id between 1 and 100000 limit 10) delete from t where ctid in (select ctid from t1);
DELETE 10

postgres=#  with t1 as (select ctid from t where id between 1 and 100000 limit 10) update t set info='new' where ctid in (select ctid from t1);
UPDATE 10

postgres=# explain with t1 as (select ctid from t where id between 1 and 100000 limit 10) update t set info='new' where ctid in (select ctid from t1);
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Update on t  (cost=129834.64..134784.79 rows=10 width=40)
   CTE t1
     ->  Limit  (cost=0.42..0.78 rows=10 width=6)
           ->  Index Scan using t_pkey on t t_1  (cost=0.42..3178.93 rows=89225 width=6)
                 Index Cond: ((id >= 1) AND (id <= 100000))
   ->  Merge Semi Join  (cost=129833.86..134784.01 rows=10 width=40)
         Merge Cond: (t.ctid = t1.ctid)
         ->  Sort  (cost=129833.49..132308.49 rows=990000 width=10)
               Sort Key: t.ctid
               ->  Seq Scan on t  (cost=0.00..14325.00 rows=990000 width=10)  -- 这个NODE非常慢
         ->  Sort  (cost=0.37..0.39 rows=10 width=36)
               Sort Key: t1.ctid
               ->  CTE Scan on t1  (cost=0.00..0.20 rows=10 width=36)
(13 rows)

这应该也是可以优化的地方，已反馈给社区。

函数+游标实现

do language plpgsql 
$$

declare 
  rowvar record;
 cur cursor for select * from t where id between 1 and 1000000 limit 10;
begin
  open cur; 
  loop 
    fetch cur into rowvar;
    if found then 
      update t set info='new' where current of cur returning * into rowvar; raise notice '%', rowvar;
    else
      return;
    end if;
  end loop; 
  close cur;
end;

$$
;

NOTICE:  00000: (10011,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10012,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10013,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10014,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10015,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10016,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10017,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10018,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10019,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10020,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
DO

期待阿里云PostgreSQL扩展支持这个MySQL语法。