> Okay, let's give it another 12 hours and I will apply it tomorrow, > thanks. I had a new thought. Perhaps the root problem is trying to put too many things into one example. We can use the two examples to show different things.
I've kept batching - with skip lock, limit and ctid - in DELETE. Other than switching out skip lock for order by, this is much the same as currently, but with all the existing update explanatory text moved here. Skip lock makes ctid safe, and this is a good use of it. I've used UPDATE to show how to prevent deadlocks with order by, for update, and an immutable key. This meant explaining why an immutable key matters, conveniently ruling out ctid without even mentioning it. I think this now covers how to order by and limit with update and delete, how and why to batch, how to safely avoid deadlocks, how to use for update (with and without skip lock) and when and when not to use ctid. But if you don't like this approach, I'm still happy if you go with your patch. I expect my patch needs quite a bit more work, even if you do like it. Thanks, Bernice
From a18ab759813fdfe30241231da41fb8cb8a97be67 Mon Sep 17 00:00:00 2001 From: Bernice Southey <[email protected]> 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 <structfield>ctid</structfield> will change if it is updated or moved by <command>VACUUM FULL</command>. Therefore - <structfield>ctid</structfield> is useless as a long-term row + <structfield>ctid</structfield> should not be used as a row identifier. A primary key should be used to identify logical rows. </para> </listitem> 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; </programlisting> </para> - <para> - While there is no <literal>LIMIT</literal> clause - for <command>DELETE</command>, it is possible to get a similar effect - using the same method described in <link linkend="update-limit">the - documentation of <command>UPDATE</command></link>: + <para id="delete-limit"> + 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 <command>VACUUM</command> + operation on the table between batches. While there is no <literal>LIMIT + </literal> clause for <command>DELETE</command>, it is possible to get a + similar effect through the use of a <link linkend="queries-with">Common + Table Expression</link> and a self-join. A related example for <literal> + ORDER BY</literal> is described in <link linkend="update-order-by">the + documentation of <command>UPDATE</command></link>. With the standard + <productname>PostgreSQL</productname> table access method, a self-join on + the system column <link linkend="ddl-system-columns-ctid">ctid</link> is + very efficient: <programlisting> 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; </programlisting> + This command will need to be repeated until no rows remain to be deleted. + Use of <literal>FOR UPDATE</literal> with <literal>SKIP LOCKED</literal> + prevents deadlocks from occurring if another command has locked the same + rows in a different order. However an independent check for remaining rows + without <literal>SKIP LOCKED</literal> will be needed to ensure that no + matching rows were overlooked. <literal>ORDER BY</literal> can be added to + prioritize which rows will be deleted. <structfield>ctid</structfield> is + safe here because <literal>FOR UPDATE</literal> with <literal>SKIP LOCKED in + </literal> <link linkend="xact-read-committed">Read Committed</link> mode + guarantees the rows in the <literal>DELETE</literal> are the same rows + returned by the <literal>SELECT</literal>. </para> </refsect1> 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; </programlisting> </para> - <para id="update-limit"> - 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 <command>VACUUM</command> - operation on the table between batches. While there is - no <literal>LIMIT</literal> clause for <command>UPDATE</command>, it is - possible to get a similar effect through the use of + <para id="update-order-by"> + 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 <literal>ORDER BY</literal> clause for <literal> + UPDATE</literal>, it is possible to get a similar effect through the use of a <link linkend="queries-with">Common Table Expression</link> and a - self-join. With the standard <productname>PostgreSQL</productname> - table access method, a self-join on the system - column <link linkend="ddl-system-columns-ctid">ctid</link> is very - efficient: + self-join. A related example for <literal>LIMIT</literal> is described in + <link linkend="delete-limit"> the documentation of <command>DELETE</command> + </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 +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; </programlisting> - This command will need to be repeated until no rows remain to be updated. - Use of an <literal>ORDER BY</literal> 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 <literal>SKIP LOCKED</literal> - can be added to the <acronym>CTE</acronym> to prevent multiple commands - from updating the same row. However, then a - final <command>UPDATE</command> without <literal>SKIP LOCKED</literal> - or <literal>LIMIT</literal> will be needed to ensure that no matching - rows were overlooked. + Use of <literal>FOR UPDATE</literal> prevents lock contention with other + update operations if they use the same ordering. In <link linkend= + "xact-read-committed">Read Committed</link> mode it's very important to use + an immutable column or (columns) for the self-join to guarantee the rows in + the <literal>UPDATE</literal> are the same rows returned by the <literal> + SELECT</literal>. </para> </refsect1> -- 2.43.0
