>
> I have changed the status of commitfest entry to "Returned with
> Feedback" as Laurenz's comments have not yet been resolved. Please
> handle the comments and update the commitfest entry accordingly.
>
>
Here's another attempt, applying Laurenz's feedback:
I removed all changes to the SELECT documentation. That might seem strange
given that the heavy lifting happens in the SELECT, but I'm working from
the assumption that people's greatest need for a ctid self-join will be
because they are trying to find the LIMIT keyword on UPDATE/DELETE and
coming up empty.
Because the join syntax is subtly different between UPDATE and DELETE, I've
kept code examples in both, but the detailed explanation is in UPDATE under
the anchor "update-limit" and the DELETE example links to it.
From 298c812838491408e6910f7535067ea147abe5fc Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huin...@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
