在PostgreSQL中实现update | delete limit

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

背景

使用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语法。

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
21天前
|
SQL Oracle 关系型数据库
实时计算 Flink版操作报错之往GREENPLUM 6 写数据,用postgresql-42.2.9.jar 报 ON CONFLICT (uuid) DO UPDATE SET 语法有问题。怎么解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
29天前
|
存储 SQL 监控
关系型数据库. 基本的DELETE语句
`SQL DELETE` 语句用于从表中删除记录,基本格式为 `DELETE FROM 表名 WHERE 条件`。`WHERE` 子句可选,指定删除特定记录。例如,`DELETE FROM students WHERE age &gt; 20;` 将删除年龄大于 20 的学生。执行前注意备份,总是使用 `WHERE` 子句以避免误删,并考虑在事务中执行以保证数据安全。大量删除可能影响性能,需谨慎操作。还要留意可能存在的触发器影响。
21 2
|
29天前
|
SQL 关系型数据库 MySQL
关系型数据库使用 DELETE 语句删除
`DELETE` SQL 语句用于从关系型数据库表中删除记录。可删除所有记录(如 `DELETE FROM students;`)或依据条件(如 `DELETE FROM students WHERE age &gt; 25;`)。某些数据库支持使用 `OUTPUT` 或 `RETURNING` 子句返回被删除记录。还能通过子查询(如基于 `failed_exams` 删除 `students` 表记录)及触发器配合执行。注意,`DELETE` 在事务中执行,需谨慎操作并确保数据备份。
27 1
|
7月前
|
关系型数据库 PostgreSQL
postgresql通过select结果进行update
postgresql通过select结果进行update
|
关系型数据库 数据库 数据安全/隐私保护
RDS 5.6 执行update更新报ERROR 1142 (42000)
RDS 5.6 执行update更新报ERROR 1142 (42000)
RDS 5.6 执行update更新报ERROR 1142 (42000)
|
SQL 存储 Oracle
PostgreSQL 事务隔离级别的实现和多版本并发控制|学习笔记
快速学习 PostgreSQL 事务隔离级别的实现和多版本并发控制
450 0
PostgreSQL 事务隔离级别的实现和多版本并发控制|学习笔记
|
关系型数据库 测试技术 PostgreSQL
postgresql实现影响分析
通过postgresql模仿分析假如城市发布通知,位于街道的人员是否受到了影响
103 0
postgresql实现影响分析
|
弹性计算 资源调度 运维
【实操系列】 AnalyticDB PostgreSQL发布实例计划管理功能,实现资源分时弹性&分时启停
本文将对AnalyticDB PostgreSQL产品的计划任务管理功能以及其背后的实现机制和最佳实践做详细介绍。
【实操系列】 AnalyticDB PostgreSQL发布实例计划管理功能,实现资源分时弹性&分时启停
|
存储 运维 Cloud Native
【实操系列】基于AnalyticDB PostgreSQL数据共享实现企业级跨多业务的敏捷分析
云数据仓库AnalyticDB PostgreSQL 版发布了最新自研的云原生架构实例,实现了跨实例间的数据共享能力。允许进行跨实例间的实时数据共享且无需进行数据迁移,可支持构建安全、高效、灵活的数据分析场景。本文介绍了依托数据共享实现云数仓跨多业务实例的敏捷数据分析方案;
【实操系列】基于AnalyticDB PostgreSQL数据共享实现企业级跨多业务的敏捷分析
|
存储 Kubernetes 负载均衡
「在 Kubernetes 上运行 Pgpool-Il」实现 PostgreSQL 查询(读)负载均衡和连接池
「在 Kubernetes 上运行 Pgpool-Il」实现 PostgreSQL 查询(读)负载均衡和连接池
312 0
「在 Kubernetes 上运行 Pgpool-Il」实现 PostgreSQL 查询(读)负载均衡和连接池

相关产品

  • 云原生数据库 PolarDB