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 <[email protected]>
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-yjneqc7r...@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 <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
