public inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedFrom: Bruce Momjian <bruce@momjian.us>
To: Bernice Southey <bernice.southey@gmail.com>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: More guidance on ctid
Date: Mon, 24 Nov 2025 16:19:26 -0500
Message-ID: <aSTL3rgmztLq1UIH@momjian.us> (raw)
In-Reply-To: <CAEDh4nyn5swFYuSfcnGAbpQrKOc47Hh_ZyKVSPYJcu2P=51Luw@mail.gmail.com>
References: <CAEDh4nyn5swFYuSfcnGAbpQrKOc47Hh_ZyKVSPYJcu2P=51Luw@mail.gmail.com>
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.
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, bernice.southey@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: More guidance on ctid
In-Reply-To: <aSTL3rgmztLq1UIH@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox