MIME-Version: 1.0
References: <aSTL3rgmztLq1UIH@momjian.us>
 <CAEDh4nzDMoRDZMDGGxKHzJ5hZscmvT2FWioTzQ6A0aJVtCO8NQ@mail.gmail.com>
 <aUnBfsbUbObEcY42@momjian.us>
 <CAEDh4nz-6HeVGHEU1Pv7Z4y=j44wtkeMyyJ5tfqAT5FHT4sq=w@mail.gmail.com>
 <aUsKZRmXgvI-k5Nb@momjian.us>
 <CAEDh4nwv22R3WBC-JW96YWfrvgA9UN=rDx11T4+zNrGVpXg5BQ@mail.gmail.com>
 <aUskW-srPCWXpi2R@momjian.us>
 <CAEDh4nyStja3R4X6qW_zpKz2w8EPUTsHQ8S0k9_saPA7BT+quw@mail.gmail.com>
 <aUwKSGHaZAd5_xKl@momjian.us>
 <CAEDh4nyBQe8rc_OGwZW-pevAXc8r2sHW6EbYzCq40ZrudxJOng@mail.gmail.com>
 <aUxDNWHaDLM3YhS0@momjian.us>
In-Reply-To: <aUxDNWHaDLM3YhS0@momjian.us>
From: Robert Treat <rob@xzilla.net>
Date: Wed, 24 Dec 2025 15:20:00 -0500
Message-ID: 
 <CABV9wwPQHSxf8ytvxW0WHP1pLmHE2iRLNRQNtXVvLswU0=2d8A@mail.gmail.com>
Subject: Re: More guidance on ctid
To: Bruce Momjian <bruce@momjian.us>
Cc: Bernice Southey <bernice.southey@gmail.com>,
 pgsql-docs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CABV9wwPQHSxf8ytvxW0WHP1pLmHE2iRLNRQNtXVvLswU0%3D2d8A%40mail.gmail.com>
Precedence: bulk

On Wed, Dec 24, 2025 at 2:47=E2=80=AFPM Bruce Momjian <bruce@momjian.us> wr=
ote:
> 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