From 298c812838491408e6910f7535067ea147abe5fc Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Sat, 3 Feb 2024 14:38:50 -0500
Subject: [PATCH v2] Documentation: Show alternatives to LIMIT on UPDATE and
 DELETE

Show examples of how to simulate UPDATE or DELETE with a LIMIT clause.

These examples also serve to show the existence and utility of ctid self-joins.
---
 doc/src/sgml/ref/delete.sgml | 18 +++++++++++++++++
 doc/src/sgml/ref/update.sgml | 38 +++++++++++++++++++++++++++++++++++-
 2 files changed, 55 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 1b81b4e7d7..21aae30e91 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -234,6 +234,24 @@ DELETE FROM films
    In some cases the join style is easier to write or faster to
    execute than the sub-select style.
   </para>
+  <para id="delete-limit">
+   While there is no <literal>LIMIT</literal> clause for
+   <command>DELETE</command>, it is possible to get a similar effect
+   using the method for <command>UPDATE</command> operations described
+   <link linkend="update-limit">in greater detail here</link>.
+<programlisting>
+WITH delete_batch AS (
+  SELECT l.ctid
+  FROM user_logs AS l
+  WHERE l.status = 'archived'
+  ORDER BY l.creation_date
+  LIMIT 10000
+  FOR UPDATE
+)
+DELETE FROM user_logs AS ul
+USING delete_branch AS del
+WHERE ul.ctid = del.ctid;
+</programlisting></para>
  </refsect1>
 
  <refsect1>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 2ab24b0523..49e0dc29de 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -434,7 +434,6 @@ UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
 COMMIT;
 </programlisting>
   </para>
-
   <para>
    Change the <structfield>kind</structfield> column of the table
    <structname>films</structname> in the row on which the cursor
@@ -442,6 +441,43 @@ COMMIT;
 <programlisting>
 UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;
 </programlisting></para>
+  <para id="update-limit">
+   Updates affecting many rows can have negative effects on system performance,
+   such as table bloat, increased replica lag, increased lock contention,
+   and possible failure of the operation due to a deadlock. In such situations
+   it can make sense to perform the operation in smaller batches. Performing a
+   <command>VACUUM</command> operation on the table in between batches can help
+   reduce table bloat. The
+   <glossterm linkend="glossary-sql-standard">SQL standard</glossterm> does
+   not define a <literal>LIMIT</literal> clause for <command>UPDATE</command>
+   operations, but it is possible get a similar effect through the use of a
+   <link linkend="queries-with">Common Table Expression</link> and an
+   efficient self-join via the system column
+   <link linkend="ddl-system-columns-ctid">ctid</link>:
+<programlisting>
+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
+  FOR UPDATE
+  LIMIT 5000
+)
+UPDATE work_item
+SET status = 'failed'
+FROM exceeded_max_retries AS emr
+WHERE work_item.ctid = emr.ctid
+</programlisting>
+    If lock contention is a concern, then <literal>SKIP LOCKED</literal> can
+    be added to the <acronym>CTE</acronym>. However, one final
+    <command>UPDATE</command> without <literal>SKIP LOCKED</literal> or
+    <literal>LIMIT</literal> will be needed to ensure that no matching rows
+    were overlooked. The use of an <literal>ORDER BY</literal> clause allows
+    the command to prioritize which rows will be locked and updated. This can
+    also reduce contention with other update operations if they use the same
+    ordering.
+  </para>
  </refsect1>
 
  <refsect1>
-- 
2.43.0