On Wed, Dec 24, 2025 at 2:47 PM Bruce Momjian <[email protected]> wrote: > On Wed, Dec 24, 2025 at 07:38:07PM +0000, Bernice Southey wrote: > > Bruce Momjian <[email protected]> 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
