>
>
> - About the style: there is usually an empty line between an ending </para>
> and the next starting <para>. It does not matter for correctness, but I
> think it makes the source easier to read.
>
Done. I've seen them with spaces and without, and have no preference.
>
> - I would rather have only "here" as link text rather than "in greater
> details
> here". Even better would be something that gives the reader a clue where
> the link will take her, like
> <link linkend="update-limit">the documentation of
> <command>UPDATE</command></link>.
>
Done.
>
> - I am not sure if it is necessary to have the <programlisting> at all.
> I'd say that it is just a trivial variation of the UPDATE example.
> On the other hand, a beginner might find the example useful.
> Not sure.
>
I think a beginner would find it useful. The join syntax for DELETE is
different from UPDATE in a way that has never made sense to me, and a
person with only the UPDATE example might try just replacing UPDATE WITH
DELETE and eliminating the SET clause, and frustration would follow. We
have an opportunity to show the equivalent join in both cases, let's use it.
> I think the "in" before between is unnecessary and had better be removed,
> but
> I'll defer to the native speaker.
>
The "in" is more common when spoken. Removed.
From a6b57bf3a88c5df614b5dede99af3e99fe8e8089 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huin...@gmail.com>
Date: Mon, 12 Feb 2024 11:32:49 -0500
Subject: [PATCH v3] 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 | 37 ++++++++++++++++++++++++++++++++++++
2 files changed, 55 insertions(+)
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 1b81b4e7d7..1544a28e18 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">the documentation of <command>UPDATE</command></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..ed3dd029c7 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -442,6 +442,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 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
