public inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedMore guidance on ctid
17+ messages / 3 participants
[nested] [flat]
* More guidance on ctid
@ 2025-11-23 10:24 Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 17+ messages in thread
From: Bernice Southey @ 2025-11-23 10:24 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
Hi,
I'm wondering if the explanation of ctid in System Columns should have
more guidance - especially as it's used in examples in the UPDATE and
DELETE docs. So far I've been caught out by partitions and concurrent
updates. This was after I changed primary keys to ctid, in self-joins
that get ORDER BY or LIMIT, for UPDATE and DELETE.
I'm too unsure about this idea and my knowledge of ctid to propose a
patch, but FWIW, here's my attempt:
Ctid is useful for removing duplicate rows and efficient self-joins.
Be aware that when ctid is used to find a row, only the first
concurrent write will be applied. Also note that ctids are not unique
across table partitions.
I'm learning my way through ctid, and the mailing lists, and am still
in the foothills of both. Sorry about the topic-adjacent duplicates in
other lists from my ramblings.
Thanks, Bernice
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
@ 2025-11-24 21:19 ` Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Bruce Momjian @ 2025-11-24 21:19 UTC (permalink / raw)
To: Bernice Southey <bernice.southey@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
On Sun, Nov 23, 2025 at 10:24:42AM +0000, Bernice Southey wrote:
> Hi,
> I'm wondering if the explanation of ctid in System Columns should have
> more guidance - especially as it's used in examples in the UPDATE and
> DELETE docs. So far I've been caught out by partitions and concurrent
> updates. This was after I changed primary keys to ctid, in self-joins
> that get ORDER BY or LIMIT, for UPDATE and DELETE.
>
> I'm too unsure about this idea and my knowledge of ctid to propose a
> patch, but FWIW, here's my attempt:
> Ctid is useful for removing duplicate rows and efficient self-joins.
> Be aware that when ctid is used to find a row, only the first
> concurrent write will be applied. Also note that ctids are not unique
> across table partitions.
>
> I'm learning my way through ctid, and the mailing lists, and am still
> in the foothills of both. Sorry about the topic-adjacent duplicates in
> other lists from my ramblings.
I didn't think we had ctid referenced in example queries, but I now see
it was added in PG 17:
commit 2daeba6a4e4
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sun Apr 7 16:26:47 2024 -0400
Doc: show how to get the equivalent of LIMIT for UPDATE/DELETE.
Add examples showing use of a CTE and a self-join to perform
partial UPDATEs and DELETEs.
Corey Huinker, reviewed by Laurenz Albe
Discussion: https://postgr.es/m/CADkLM=caNEQsUwPWnfi2jR4ix99E0EJM_3jtcE-YjnEQC7Rssw@mail.gmail.com
The examples use ctid to show how you can do incremental UPDATEs and
DELETEs, to avoid excessive cleanup/vacuum requirements. While that
certainly is a good idea, it only works because the common table
expression examples, by definition, operate in the same snapshot.
I think the reporter above extrapolated this example to use ctid in
other, non-appropriate cases. Should we add a warning to the docs to
explain that the general use of ctid is discouraged?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
@ 2025-11-29 22:45 ` Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 17+ messages in thread
From: Bernice Southey @ 2025-11-29 22:45 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs@lists.postgresql.org
Bruce Momjian <bruce@momjian.us> wrote:
> I think the reporter above extrapolated this example to use ctid in
> other, non-appropriate cases. Should we add a warning to the docs to
> explain that the general use of ctid is discouraged?
I've been explaining this badly. Here's an example that I hope will
make clear why I'm so concerned.
(Read Committed default isolation level)
drop table if exists b1, b2;
create table b1 as select 1 id, 100 balance;
create table b2 as select 1 id, 100 balance;
With an immutable column self-join, it works as expected and
serializes the balance changes.
--session1
begin;
with x as (select id from b1 order by id for update)
update b1 set balance = balance + 1 from x where b1.id = x.id;
--session2
with x as (select id from b1 order by id for update)
update b1 set balance = balance - 100 from x where b1.id = x.id;
select * from b1;
--session1
commit;
--session2
UPDATE 1
id | balance
----+---------
1 | 1
(1 row)
But with ctid, the second update is lost.
--session1
begin;
with x as (select ctid from b2 order by id for update)
update b2 set balance = balance + 1 from x where b2.ctid = x.ctid;
--session2
with x as (select ctid from b2 order by id for update)
update b2 set balance = balance - 100 from x where b2.ctid = x.ctid;
select * from b2;
--session1
commit;
--session2
UPDATE 0
id | balance
----+---------
1 | 101
(1 row)
The session2 CTE blocks until it gets the updated version of the row
from session 1 commit. It gets b2.ctid = (0,2) giving x.ctid = (0,2).
But the UPDATE gets b2.ctid = (0,1) at the start of the transaction.
This doesn't change even after session 1 commits. The join fails. This
is correct, but not obvious.
This is why I think the docs should say something different to what
they currently do. The UPDATE example is fine only because it's run
repeatedly until there are no more rows to find.
I learned the primary key self-join pattern for order by/limit in
delete/update soon after I began with postgres. I'm pretty sure ctid
would have confused me. So I think the doc examples with an id column
will be very helpful, and safer.
If users discover ctid on internet forums or LLMs and then check the
docs, I think they should get some notice of the concurrency risk.
Here's a patch with an attempt to do the above.
Forum examples:
https://stackoverflow.com/questions/10245560/deadlocks-in-postgresql-when-running-update/71163671#71...
https://www.reddit.com/r/PostgreSQL/comments/1cn0q1c/comment/l36ppve/
Thanks, Bernice
Attachments:
[text/x-patch] v1-add-ctid-guidance-and-remove-from-examples.patch (3.1K, 2-v1-add-ctid-guidance-and-remove-from-examples.patch)
download | inline diff:
From 69595aeb9f1e6eda74fa29fde22bd117a8044812 Mon Sep 17 00:00:00 2001
From: Bernice Southey <bernice.southey@gmail.com>
Date: Thu, 20 Nov 2025 11:53:57 +0000
Subject: [PATCH] Add ctid guidance and remove from examples
---
doc/src/sgml/ddl.sgml | 5 ++++-
doc/src/sgml/ref/delete.sgml | 10 +++++-----
doc/src/sgml/ref/update.sgml | 9 +++------
3 files changed, 12 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 09ad8400fd0..99c8facfe0b 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1559,7 +1559,10 @@ CREATE TABLE circles (
<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
- identifier. A primary key should be used to identify logical rows.
+ identifier. Be aware that ctids will change in data-modifying
+ concurrent transactions, and they are not unique across table partitions.
+ A primary key should be used to identify logical rows.
+
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 5b52f77e28f..48eb1b6755a 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -313,15 +313,15 @@ DELETE FROM tasks WHERE CURRENT OF c_tasks;
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
+ SELECT a.id FROM accounts AS a
+ WHERE a.status = 'archived'
+ ORDER BY a.id
FOR UPDATE
LIMIT 10000
)
-DELETE FROM user_logs AS dl
+DELETE FROM accounts AS dl
USING delete_batch AS del
- WHERE dl.ctid = del.ctid;
+ WHERE dl.id = del.id;
</programlisting>
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 40cca063946..7374b884080 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -486,13 +486,10 @@ UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;
no <literal>LIMIT</literal> clause for <command>UPDATE</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. 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:
<programlisting>
WITH exceeded_max_retries AS (
- SELECT w.ctid FROM work_item AS w
+ SELECT w.id FROM work_item AS w
WHERE w.status = 'active' AND w.num_retries > 10
ORDER BY w.retry_timestamp
FOR UPDATE
@@ -500,7 +497,7 @@ WITH exceeded_max_retries AS (
)
UPDATE work_item SET status = 'failed'
FROM exceeded_max_retries AS emr
- WHERE work_item.ctid = emr.ctid;
+ WHERE work_item.id = emr.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
--
2.43.0
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
@ 2025-12-22 22:09 ` Bruce Momjian <bruce@momjian.us>
2025-12-23 11:49 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Bruce Momjian @ 2025-12-22 22:09 UTC (permalink / raw)
To: Bernice Southey <bernice.southey@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
On Sat, Nov 29, 2025 at 10:45:45PM +0000, Bernice Southey wrote:
> Bruce Momjian <bruce@momjian.us> wrote:
> This is why I think the docs should say something different to what
> they currently do. The UPDATE example is fine only because it's run
> repeatedly until there are no more rows to find.
>
> I learned the primary key self-join pattern for order by/limit in
> delete/update soon after I began with postgres. I'm pretty sure ctid
> would have confused me. So I think the doc examples with an id column
> will be very helpful, and safer.
>
> If users discover ctid on internet forums or LLMs and then check the
> docs, I think they should get some notice of the concurrency risk.
>
> Here's a patch with an attempt to do the above.
I was traveling so I apologize for the delayed reply.
Sorry, I didn't like your proposed patch because it removes the ctid
example, and I didn't write that example so I don't feel enabled to
remove it. What I did do was to explain why ctid was safe in this
use-case. I also strengthened the documentation that ctid is not valid
for row references.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
Attachments:
[text/x-diff] ctid.diff (1.8K, 2-ctid.diff)
download | inline diff:
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..c8dce945b06 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -323,6 +323,9 @@ DELETE FROM user_logs AS dl
USING delete_batch AS del
WHERE dl.ctid = del.ctid;
</programlisting>
+ This use of <structfield>ctid</structfield> is only safe because
+ the query is repeately run, avoiding the problem of changed
+ <structfield>ctid</structfield>s.
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 40cca063946..289f48b2549 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -503,6 +503,9 @@ UPDATE work_item SET status = 'failed'
WHERE work_item.ctid = emr.ctid;
</programlisting>
This command will need to be repeated until no rows remain to be updated.
+ (This use of <structfield>ctid</structfield> is only safe because
+ the query is repeately run, avoiding the problem of changed
+ <structfield>ctid</structfield>s.)
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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
@ 2025-12-23 11:49 ` Bernice Southey <bernice.southey@gmail.com>
2025-12-23 21:32 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 17+ messages in thread
From: Bernice Southey @ 2025-12-23 11:49 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs@lists.postgresql.org
Bruce Momjian <bruce@momjian.us> wrote:
> What I did do was to explain why ctid was safe in this
> use-case. I also strengthened the documentation that ctid is not valid
> for row references.
I like this, explaining the risk is much better. I couldn't think of a
simple way to do it.
There was a strong preference for not duplicating the explanatory text
when the examples were added. Perhaps delete doesn't need the new
line?
Thanks, Bernice
PS - Thanks for your postgres internals videos. I found them a few
months ago and they lit up my brain.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 11:49 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
@ 2025-12-23 21:32 ` Bruce Momjian <bruce@momjian.us>
2025-12-23 22:27 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Bruce Momjian @ 2025-12-23 21:32 UTC (permalink / raw)
To: Bernice Southey <bernice.southey@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
On Tue, Dec 23, 2025 at 11:49:37AM +0000, Bernice Southey wrote:
> Bruce Momjian <bruce@momjian.us> wrote:
> > What I did do was to explain why ctid was safe in this
> > use-case. I also strengthened the documentation that ctid is not valid
> > for row references.
> I like this, explaining the risk is much better. I couldn't think of a
> simple way to do it.
Great.
> There was a strong preference for not duplicating the explanatory text
> when the examples were added. Perhaps delete doesn't need the new
> line?
Uh, where did you see that? I found the original thread and I don't see
any mention of a warning, or not wanting a warning:
https://www.postgresql.org/message-id/flat/CADkLM%3DcaNEQsUwPWnfi2jR4ix99E0EJM_3jtcE-YjnEQC7Rssw%40m...
I feel we need a warning specifically because people will find this
query, particularly in the very visible UPDATE/DELETE man pages, and not
realize ctid only has a few save use-cases.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 11:49 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 21:32 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
@ 2025-12-23 22:27 ` Bernice Southey <bernice.southey@gmail.com>
2025-12-23 23:23 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 17+ messages in thread
From: Bernice Southey @ 2025-12-23 22:27 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs@lists.postgresql.org
Bruce Momjian <bruce@momjian.us> wrote:
> Uh, where did you see that? I found the original thread and I don't see
> any mention of a warning, or not wanting a warning:
I meant about all the other stuff (order by and limit etc). At one
point the patch had more duplication across update and delete.
> I feel we need a warning specifically because people will find this
> query, particularly in the very visible UPDATE/DELETE man pages, and not
> realize ctid only has a few save use-cases.
Yes.
I only queried adding it to both because I remembered the original
thread. I agree the visibility of the duplicate warning is better.
[From the thread]
> > It is similar, but the idea here is to aid in discovery. A user might miss the
> > technique for update if it's only documented in delete, and even if they did see
> > it there, they might not realize that it works for both UPDATE and DELETE.
> > We could make reference links from one to the other, but that seems like extra
> > work for the reader.
> I don't agree with bloating the documentation with redundant examples just
> to save a user a click. I like the idea of a link
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 11:49 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 21:32 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 22:27 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
@ 2025-12-23 23:23 ` Bruce Momjian <bruce@momjian.us>
2025-12-24 15:26 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Bruce Momjian @ 2025-12-23 23:23 UTC (permalink / raw)
To: Bernice Southey <bernice.southey@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
On Tue, Dec 23, 2025 at 10:27:55PM +0000, Bernice Southey wrote:
> Bruce Momjian <bruce@momjian.us> wrote:
> > Uh, where did you see that? I found the original thread and I don't see
> > any mention of a warning, or not wanting a warning:
> I meant about all the other stuff (order by and limit etc). At one
> point the patch had more duplication across update and delete.
>
> > I feel we need a warning specifically because people will find this
> > query, particularly in the very visible UPDATE/DELETE man pages, and not
> > realize ctid only has a few save use-cases.
> Yes.
> I only queried adding it to both because I remembered the original
> thread. I agree the visibility of the duplicate warning is better.
>
> [From the thread]
> > > It is similar, but the idea here is to aid in discovery. A user might miss the
> > > technique for update if it's only documented in delete, and even if they did see
> > > it there, they might not realize that it works for both UPDATE and DELETE.
> > > We could make reference links from one to the other, but that seems like extra
> > > work for the reader.
> > I don't agree with bloating the documentation with redundant examples just
> > to save a user a click. I like the idea of a link
Okay, let's give it another 12 hours and I will apply it tomorrow,
thanks.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 11:49 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 21:32 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 22:27 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 23:23 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
@ 2025-12-24 15:26 ` Bernice Southey <bernice.southey@gmail.com>
2025-12-24 15:44 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 17+ messages in thread
From: Bernice Southey @ 2025-12-24 15:26 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs@lists.postgresql.org
> 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
Attachments:
[text/x-patch] v3-rework-update-and-delete-self-join-examples.patch (6.8K, 2-v3-rework-update-and-delete-self-join-examples.patch)
download | inline diff:
From a18ab759813fdfe30241231da41fb8cb8a97be67 Mon Sep 17 00:00:00 2001
From: Bernice Southey <bernice.southey@gmail.com>
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 11:49 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 21:32 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 22:27 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 23:23 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 15:26 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
@ 2025-12-24 15:44 ` Bruce Momjian <bruce@momjian.us>
2025-12-24 17:46 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-24 19:38 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Bruce Momjian @ 2025-12-24 15:44 UTC (permalink / raw)
To: Bernice Southey <bernice.southey@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
On Wed, Dec 24, 2025 at 03:26:23PM +0000, Bernice Southey wrote:
> > 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.
We could go in the direction you suggested, but it seems out-of-place in
the UPDATE/DELETE docs since it gets into a lot of details. Maybe in
the locking chapter?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 11:49 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 21:32 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 22:27 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 23:23 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 15:26 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-24 15:44 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
@ 2025-12-24 17:46 ` Bernice Southey <bernice.southey@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Bernice Southey @ 2025-12-24 17:46 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs@lists.postgresql.org
Bruce Momjian <bruce@momjian.us> wrote:
> We could go in the direction you suggested, but it seems out-of-place in
> the UPDATE/DELETE docs since it gets into a lot of details. Maybe in
> the locking chapter?
hmmmmm...the original examples were introduced to show people how to
work around no order by and limit for update and delete. That
capability would of course be the simplest solution for everyone to
understand and would hide all this locking trouble. If only it was
simple to add. But in the absence of this, the cte select pattern does
work as a substitute. Since the complexity cat is already out the bag
with these examples in UPDATE and DELETE, showing how to use the
work-around properly seems responsible and worth it. The hard part is
keeping it simple.
In a different life when I was a service developer, my excellent SQL
Server data architect told me the only way to avoid deadlocks on
multirow updates was retries. This didn't work. Deadlocks were the
bane of our system. A couple of years ago my very experienced partner
rearchitected part of his Postgres system after deadlocks killed the
performance. He was unaware deadlocks were caused by ordering. (It was
his fist postgres system.) I expect many systems prematurely avoid
batching because of deadlocks, when all they need is ordering. This is
a pity because batching is brilliant for performance when done right.
This history is why I'm keen on properly explaining how to avoid
deadlocks. I ran headlong into the locking issues using this cte
select pattern because I had an improper understanding of the locking.
One of the last things I added in my patch was the link to the MMVC
doc and maybe this level of detail is unnecessary. Maybe there's a way
to phrase this all that's less intimidating. The rule of thumb is use
skip locked with ctid and otherwise your primary key, and then you
should be fine with this pattern. To introduce these examples where
your rows can change underneath you without some warning, is
problematic.
Those are my Christmas eve thoughts because I go eat my delicious
dinner. Have a lovely holiday!
Thanks, Bernice
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 11:49 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 21:32 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 22:27 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 23:23 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 15:26 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-24 15:44 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
@ 2025-12-24 19:38 ` Bernice Southey <bernice.southey@gmail.com>
2025-12-24 19:47 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 17+ messages in thread
From: Bernice Southey @ 2025-12-24 19:38 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs@lists.postgresql.org
Bruce Momjian <bruce@momjian.us> wrote:
> We could go in the direction you suggested, but it seems out-of-place in
> the UPDATE/DELETE docs since it gets into a lot of details. Maybe in
> the locking chapter?
How about if the UPDATE and DELETE examples only show how to get limit
and order by with a cte, and remove all references to locking. No for
update, deadlocks etc. The examples use primary keys and not ctid.
Anyone just trying to do simple limit and order by without locking
problems will get what they need, and won't be confused by the locking
complexity. Anyone trying to solve lock contention needs to understand
locking and should be looking at that chapter. The explanation for
deadlock avoidance should be there as you suggest. Perhaps the update
and delete examples can link to them. If you think this is the right
approach I'm willing to give it a go?
Thanks, Bernice
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 11:49 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 21:32 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 22:27 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 23:23 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 15:26 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-24 15:44 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 19:38 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
@ 2025-12-24 19:47 ` Bruce Momjian <bruce@momjian.us>
2025-12-24 20:20 ` Re: More guidance on ctid Robert Treat <rob@xzilla.net>
2025-12-24 20:31 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Bruce Momjian @ 2025-12-24 19:47 UTC (permalink / raw)
To: Bernice Southey <bernice.southey@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
On Wed, Dec 24, 2025 at 07:38:07PM +0000, Bernice Southey wrote:
> Bruce Momjian <bruce@momjian.us> wrote:
> > We could go in the direction you suggested, but it seems out-of-place in
> > the UPDATE/DELETE docs since it gets into a lot of details. Maybe in
> > the locking chapter?
> How about if the UPDATE and DELETE examples only show how to get limit
> and order by with a cte, and remove all references to locking. No for
> update, deadlocks etc. The examples use primary keys and not ctid.
> Anyone just trying to do simple limit and order by without locking
> problems will get what they need, and won't be confused by the locking
> complexity. Anyone trying to solve lock contention needs to understand
> locking and should be looking at that chapter. The explanation for
> deadlock avoidance should be there as you suggest. Perhaps the update
> and delete examples can link to them. If you think this is the right
> approach I'm willing to give it a go?
I am not the author of the original ctid doc patch, but I believe the
goal was to use ctid so we don't need to use needless index lookups for
primary keys.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 11:49 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 21:32 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 22:27 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 23:23 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 15:26 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-24 15:44 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 19:38 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-24 19:47 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
@ 2025-12-24 20:20 ` Robert Treat <rob@xzilla.net>
2025-12-25 16:06 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Robert Treat @ 2025-12-24 20:20 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Bernice Southey <bernice.southey@gmail.com>; pgsql-docs@lists.postgresql.org
On Wed, Dec 24, 2025 at 2:47 PM Bruce Momjian <bruce@momjian.us> wrote:
> On Wed, Dec 24, 2025 at 07:38:07PM +0000, Bernice Southey wrote:
> > Bruce Momjian <bruce@momjian.us> wrote:
> > > We could go in the direction you suggested, but it seems out-of-place in
> > > the UPDATE/DELETE docs since it gets into a lot of details. Maybe in
> > > the locking chapter?
> > How about if the UPDATE and DELETE examples only show how to get limit
> > and order by with a cte, and remove all references to locking. No for
> > update, deadlocks etc. The examples use primary keys and not ctid.
> > Anyone just trying to do simple limit and order by without locking
> > problems will get what they need, and won't be confused by the locking
> > complexity. Anyone trying to solve lock contention needs to understand
> > locking and should be looking at that chapter. The explanation for
> > deadlock avoidance should be there as you suggest. Perhaps the update
> > and delete examples can link to them. If you think this is the right
> > approach I'm willing to give it a go?
>
> I am not the author of the original ctid doc patch, but I believe the
> goal was to use ctid so we don't need to use needless index lookups for
> primary keys.
>
I think there were multiple goals at play, but IMHO they resulted in
an example that was too clever by half. While I have used multiple
versions of the technique they were trying to highlight myself, I
think it is out of place to add such complex examples in the
documentation where we are relying on the behavioral side-effects
(locking and ordering) of what is essentially an implementation detail
(ctid) and a detail which we really do not recommend users interact
with in any general way.
If we just focus on the original goal of providing an UPDATE with
LIMIT alternative, there are multiple ways to achieve that with much
simpler (non-CTE event) syntax. To the degree people feel those
solutions don't scale, that's entirely dependent on a combination of
factors like schema design, size of data, overall read and write
throughputs, among others; things that feel fairly out of scope for
what is essentially the grammar portion of the documentation. If
people want to add caveats around working at scale, I think Bernice is
on the right path by suggesting those things be written about in
various other sections of the documentation. While it may seem better
to jam all those bits together, it is easy to forget it comes at a
cost of complexity for most users, again, which Bernice is a good
example of.
Robert Treat
https://xzilla.net
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 11:49 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 21:32 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 22:27 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 23:23 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 15:26 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-24 15:44 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 19:38 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-24 19:47 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 20:20 ` Re: More guidance on ctid Robert Treat <rob@xzilla.net>
@ 2025-12-25 16:06 ` Bernice Southey <bernice.southey@gmail.com>
2025-12-26 22:35 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 17+ messages in thread
From: Bernice Southey @ 2025-12-25 16:06 UTC (permalink / raw)
To: Robert Treat <rob@xzilla.net>; +Cc: Bruce Momjian <bruce@momjian.us>; pgsql-docs@lists.postgresql.org
Robert Treat <rob@xzilla.net> wrote:
> I think there were multiple goals at play, but IMHO they resulted in
> an example that was too clever by half. While I have used multiple
> versions of the technique they were trying to highlight myself, I
> think it is out of place to add such complex examples in the
> documentation where we are relying on the behavioral side-effects
> (locking and ordering) of what is essentially an implementation detail
> (ctid) and a detail which we really do not recommend users interact
> with in any general way.
Thanks for this. Now I see why trying to fix these examples is so
hard. They're obviously in the wrong place. Like you, I use this
technique extensively, with and without ctid, and so I fully agree
with the patch writer's aim. As I understand, it's to encourage
batching for the performance benefits, how to resolve the inevitable
deadlocks, and the added boost of ctid.
What if we remove the examples from update and delete completely?
Instead we create a new subsection in the Performance Tips chapter
called Batching. This keeps all this good advice together, in a place
people like me, who wanted this guidance, will go looking. This is
preferable to splitting it up into unread unrelated corners of the
docs. The Batching doc could be the current UPDATE doc text expanded.
It can properly explain the locking options, it can briefly explain
what ctid is, why it's fast, and how to use it safely with locks.
I'd also like to propose including another batching trick in this new
section: using copy to populate reusable session temp tables for batch
processing. I expect there are other useful batching patterns
community members can contribute in future.
I also considered a new "Updates" section in Performance Tips, a bit
like the populating a database section. But this would need lots of
other additions (like minimising updates, checking if a record is
actually changed, HOT updates, truncating partitions....), and it
could become incohesive. I'm sure there's other potential places I'm
unaware of.
Unfortunately I was wrong about the examples in UPDATE and DELETE
being a safe use of ctid because they're called repeatedly - the final
update/delete calls aren't safe. The examples as written have the same
problem they describe for skip locked, i.e. a final execution is
needed for any missed rows. Using a select for update wait lock, with
a ctid self-join, is the equivalent of "wait skip".
Thanks, Bernice
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 11:49 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 21:32 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 22:27 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 23:23 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 15:26 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-24 15:44 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 19:38 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-24 19:47 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 20:20 ` Re: More guidance on ctid Robert Treat <rob@xzilla.net>
2025-12-25 16:06 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
@ 2025-12-26 22:35 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 17+ messages in thread
From: Bruce Momjian @ 2025-12-26 22:35 UTC (permalink / raw)
To: Bernice Southey <bernice.southey@gmail.com>; +Cc: Robert Treat <rob@xzilla.net>; pgsql-docs@lists.postgresql.org
On Thu, Dec 25, 2025 at 04:06:52PM +0000, Bernice Southey wrote:
> Robert Treat <rob@xzilla.net> wrote:
> > I think there were multiple goals at play, but IMHO they resulted in
> > an example that was too clever by half. While I have used multiple
> > versions of the technique they were trying to highlight myself, I
> > think it is out of place to add such complex examples in the
> > documentation where we are relying on the behavioral side-effects
> > (locking and ordering) of what is essentially an implementation detail
> > (ctid) and a detail which we really do not recommend users interact
> > with in any general way.
>
> Thanks for this. Now I see why trying to fix these examples is so
> hard. They're obviously in the wrong place. Like you, I use this
> technique extensively, with and without ctid, and so I fully agree
> with the patch writer's aim. As I understand, it's to encourage
> batching for the performance benefits, how to resolve the inevitable
> deadlocks, and the added boost of ctid.
Yes, the issue is that a lot of discussion went into the existing docs,
so even though they are not ideal, we don't want to over-react and
change them more than reasonable, because then you need another set of
changes to adjust them to reasonable.
Also, original reporters tend to think the problem is worse than actual
because they had the problem. This example has been published since PG
17 and this is the first reported complaint, and frankly the complaint
is that inaccurate assumptions were made from the example, and not
warned about.
Original reporters often want to add a lot of text to avoid others
having similar problem, even when clearly very few people have had the
problem.
> What if we remove the examples from update and delete completely?
> Instead we create a new subsection in the Performance Tips chapter
> called Batching. This keeps all this good advice together, in a place
> people like me, who wanted this guidance, will go looking. This is
> preferable to splitting it up into unread unrelated corners of the
> docs. The Batching doc could be the current UPDATE doc text expanded.
> It can properly explain the locking options, it can briefly explain
> what ctid is, why it's fast, and how to use it safely with locks.
>
> I'd also like to propose including another batching trick in this new
> section: using copy to populate reusable session temp tables for batch
> processing. I expect there are other useful batching patterns
> community members can contribute in future.
>
> I also considered a new "Updates" section in Performance Tips, a bit
> like the populating a database section. But this would need lots of
> other additions (like minimizing updates, checking if a record is
> actually changed, HOT updates, truncating partitions....), and it
> could become incohesive. I'm sure there's other potential places I'm
> unaware of.
Yep, "incohesive" is the risk. Right now the UPDATE and DELETE examples
are different enough that explaining them in a separate section could
be confusing.
> Unfortunately I was wrong about the examples in UPDATE and DELETE
> being a safe use of ctid because they're called repeatedly - the final
> update/delete calls aren't safe. The examples as written have the same
> problem they describe for skip locked, i.e. a final execution is
> needed for any missed rows. Using a select for update wait lock, with
> a ctid self-join, is the equivalent of "wait skip".
Yes, these are hard to get right. We already have users running the
query repeatedly, so adding SKIP LOCKED to all the queries but the last
one is certainly possible and explainable.
At this point I have applied the attached patch back to PG 17 to
highlight that "ctid" is used in the update/delete queries only. If we
want a new section or to move things around, that will only be done in
master, so it makes sense to just fix what we have now.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
Attachments:
[text/x-diff] master.diff (1.8K, 2-master.diff)
download | inline diff:
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..b9367f2b23c 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -323,6 +323,9 @@ DELETE FROM user_logs AS dl
USING delete_batch AS del
WHERE dl.ctid = del.ctid;
</programlisting>
+ This use of <structfield>ctid</structfield> is only safe because
+ the query is repeatedly run, avoiding the problem of changed
+ <structfield>ctid</structfield>s.
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 40cca063946..b523766abe3 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -503,6 +503,9 @@ UPDATE work_item SET status = 'failed'
WHERE work_item.ctid = emr.ctid;
</programlisting>
This command will need to be repeated until no rows remain to be updated.
+ (This use of <structfield>ctid</structfield> is only safe because
+ the query is repeatedly run, avoiding the problem of changed
+ <structfield>ctid</structfield>s.)
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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: More guidance on ctid
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 11:49 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 21:32 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-23 22:27 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-23 23:23 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 15:26 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-24 15:44 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
2025-12-24 19:38 ` Re: More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-12-24 19:47 ` Re: More guidance on ctid Bruce Momjian <bruce@momjian.us>
@ 2025-12-24 20:31 ` Bernice Southey <bernice.southey@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Bernice Southey @ 2025-12-24 20:31 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs@lists.postgresql.org
Bruce Momjian <bruce@momjian.us> wrote:
> I am not the author of the original ctid doc patch, but I believe the
> goal was to use ctid so we don't need to use needless index lookups for
> primary keys.
Yes, you are right, this was one of the goals. The other initial goals
were to stop people complaining about no order by and limit, and to
encourage batching. Avoiding deadlocks was very much discussed, but
not listed as one of the primary initial goals by the patch writer, if
memory serves. I was just thinking I prefer keeping ctid in lock free
examples with your new warning. ctid is safe without concurrency and
for batching to completion. There's an argument for moving the
deadlock stuff into the locking chapter because as soon as one does a
select and then an update in a concurrent world, one is guaranteed to
lose rows with ctid or any mutable key. If you think I'm overly
concerned, and the examples are fine with the locking and ctid and a
warning, I concede. The locking chapter does have good guidance on
ordering already.
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2025-12-26 22:35 UTC | newest]
Thread overview: 17+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-23 10:24 More guidance on ctid Bernice Southey <bernice.southey@gmail.com>
2025-11-24 21:19 ` Bruce Momjian <bruce@momjian.us>
2025-11-29 22:45 ` Bernice Southey <bernice.southey@gmail.com>
2025-12-22 22:09 ` Bruce Momjian <bruce@momjian.us>
2025-12-23 11:49 ` Bernice Southey <bernice.southey@gmail.com>
2025-12-23 21:32 ` Bruce Momjian <bruce@momjian.us>
2025-12-23 22:27 ` Bernice Southey <bernice.southey@gmail.com>
2025-12-23 23:23 ` Bruce Momjian <bruce@momjian.us>
2025-12-24 15:26 ` Bernice Southey <bernice.southey@gmail.com>
2025-12-24 15:44 ` Bruce Momjian <bruce@momjian.us>
2025-12-24 17:46 ` Bernice Southey <bernice.southey@gmail.com>
2025-12-24 19:38 ` Bernice Southey <bernice.southey@gmail.com>
2025-12-24 19:47 ` Bruce Momjian <bruce@momjian.us>
2025-12-24 20:20 ` Robert Treat <rob@xzilla.net>
2025-12-25 16:06 ` Bernice Southey <bernice.southey@gmail.com>
2025-12-26 22:35 ` Bruce Momjian <bruce@momjian.us>
2025-12-24 20:31 ` Bernice Southey <bernice.southey@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox