From a18ab759813fdfe30241231da41fb8cb8a97be67 Mon Sep 17 00:00:00 2001 From: Bernice Southey Date: Wed, 24 Dec 2025 15:13:00 +0000 Subject: [PATCH] rework update and delete self-join examples --- doc/src/sgml/ddl.sgml | 2 +- doc/src/sgml/ref/delete.sgml | 33 ++++++++++++++++++----- doc/src/sgml/ref/update.sgml | 51 +++++++++++++++--------------------- 3 files changed, 48 insertions(+), 38 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index cea28c00f8a..9070aaa5a7c 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1558,7 +1558,7 @@ CREATE TABLE circles ( locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore - ctid is useless as a long-term row + ctid should not be used as a row identifier. A primary key should be used to identify logical rows. diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml index 5b52f77e28f..6a512b6bbaa 100644 --- a/doc/src/sgml/ref/delete.sgml +++ b/doc/src/sgml/ref/delete.sgml @@ -306,23 +306,42 @@ DELETE FROM tasks WHERE CURRENT OF c_tasks; - - While there is no LIMIT clause - for DELETE, it is possible to get a similar effect - using the same method described in the - documentation of UPDATE: + + Deletes and updates affecting many rows can have negative effects on system + performance, such as table bloat, increased replica lag, and increased + lock contention. In such situations it can make sense to perform the + operation in smaller batches, possibly with a VACUUM + operation on the table between batches. While there is no LIMIT + clause for DELETE, it is possible to get a + similar effect through the use of a Common + Table Expression and a self-join. A related example for + ORDER BY is described in the + documentation of UPDATE. With the standard + PostgreSQL table access method, a self-join on + the system column ctid is + very efficient: WITH delete_batch AS ( SELECT l.ctid FROM user_logs AS l WHERE l.status = 'archived' - ORDER BY l.creation_date - FOR UPDATE + FOR UPDATE SKIP LOCKED LIMIT 10000 ) DELETE FROM user_logs AS dl USING delete_batch AS del WHERE dl.ctid = del.ctid; + This command will need to be repeated until no rows remain to be deleted. + Use of FOR UPDATE with SKIP LOCKED + prevents deadlocks from occurring if another command has locked the same + rows in a different order. However an independent check for remaining rows + without SKIP LOCKED will be needed to ensure that no + matching rows were overlooked. ORDER BY can be added to + prioritize which rows will be deleted. ctid is + safe here because FOR UPDATE with SKIP LOCKED in + Read Committed mode + guarantees the rows in the DELETE are the same rows + returned by the SELECT. diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index 40cca063946..24a814bc633 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -477,41 +477,32 @@ UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films; - - Updates affecting many rows can have negative effects on system - performance, such as table bloat, increased replica lag, and increased - lock contention. In such situations it can make sense to perform the - operation in smaller batches, possibly with a VACUUM - operation on the table between batches. While there is - no LIMIT clause for UPDATE, it is - possible to get a similar effect through the use of + + Updating or deleting multiple rows in the same table at the same time often + causes deadlocks. This can be solved by locking the rows in a consistent + order. While there is no ORDER BY clause for + UPDATE, it is possible to get a similar effect through the use of a Common Table Expression and a - self-join. With the standard PostgreSQL - table access method, a self-join on the system - column ctid is very - efficient: + self-join. A related example for LIMIT is described in + the documentation of DELETE + : -WITH exceeded_max_retries AS ( - SELECT w.ctid FROM work_item AS w - WHERE w.status = 'active' AND w.num_retries > 10 - ORDER BY w.retry_timestamp +WITH lock_jobs AS ( + SELECT id FROM jobs + JOIN complete_jobs USING (id) + ORDER BY id FOR UPDATE - LIMIT 5000 ) -UPDATE work_item SET status = 'failed' - FROM exceeded_max_retries AS emr - WHERE work_item.ctid = emr.ctid; +UPDATE jobs j SET status = l.status + FROM lock_jobs AS l + WHERE j.id = l.id; - This command will need to be repeated until no rows remain to be updated. - Use of an ORDER BY clause allows the command to - prioritize which rows will be updated; it can also prevent deadlock - with other update operations if they use the same ordering. - If lock contention is a concern, then SKIP LOCKED - can be added to the CTE to prevent multiple commands - from updating the same row. However, then a - final UPDATE without SKIP LOCKED - or LIMIT will be needed to ensure that no matching - rows were overlooked. + Use of FOR UPDATE prevents lock contention with other + update operations if they use the same ordering. In Read Committed mode it's very important to use + an immutable column or (columns) for the self-join to guarantee the rows in + the UPDATE are the same rows returned by the + SELECT. -- 2.43.0