Date: Mon, 24 Nov 2025 16:19:26 -0500
From: Bruce Momjian <bruce@momjian.us>
To: Bernice Southey <bernice.southey@gmail.com>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: More guidance on ctid
Message-ID: <aSTL3rgmztLq1UIH@momjian.us>
References: 
 <CAEDh4nyn5swFYuSfcnGAbpQrKOc47Hh_ZyKVSPYJcu2P=51Luw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAEDh4nyn5swFYuSfcnGAbpQrKOc47Hh_ZyKVSPYJcu2P=51Luw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/aSTL3rgmztLq1UIH%40momjian.us>
Precedence: bulk

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.