Robert Treat <[email protected]> wrote: > 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.
Thanks for this. Now I see why trying to fix these examples is so hard. They're obviously in the wrong place. Like you, I use this technique extensively, with and without ctid, and so I fully agree with the patch writer's aim. As I understand, it's to encourage batching for the performance benefits, how to resolve the inevitable deadlocks, and the added boost of ctid. What if we remove the examples from update and delete completely? Instead we create a new subsection in the Performance Tips chapter called Batching. This keeps all this good advice together, in a place people like me, who wanted this guidance, will go looking. This is preferable to splitting it up into unread unrelated corners of the docs. The Batching doc could be the current UPDATE doc text expanded. It can properly explain the locking options, it can briefly explain what ctid is, why it's fast, and how to use it safely with locks. I'd also like to propose including another batching trick in this new section: using copy to populate reusable session temp tables for batch processing. I expect there are other useful batching patterns community members can contribute in future. I also considered a new "Updates" section in Performance Tips, a bit like the populating a database section. But this would need lots of other additions (like minimising updates, checking if a record is actually changed, HOT updates, truncating partitions....), and it could become incohesive. I'm sure there's other potential places I'm unaware of. Unfortunately I was wrong about the examples in UPDATE and DELETE being a safe use of ctid because they're called repeatedly - the final update/delete calls aren't safe. The examples as written have the same problem they describe for skip locked, i.e. a final execution is needed for any missed rows. Using a select for update wait lock, with a ctid self-join, is the equivalent of "wait skip". Thanks, Bernice
