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? hmmmmm...the original examples were introduced to show people how to work around no order by and limit for update and delete. That capability would of course be the simplest solution for everyone to understand and would hide all this locking trouble. If only it was simple to add. But in the absence of this, the cte select pattern does work as a substitute. Since the complexity cat is already out the bag with these examples in UPDATE and DELETE, showing how to use the work-around properly seems responsible and worth it. The hard part is keeping it simple.
In a different life when I was a service developer, my excellent SQL Server data architect told me the only way to avoid deadlocks on multirow updates was retries. This didn't work. Deadlocks were the bane of our system. A couple of years ago my very experienced partner rearchitected part of his Postgres system after deadlocks killed the performance. He was unaware deadlocks were caused by ordering. (It was his fist postgres system.) I expect many systems prematurely avoid batching because of deadlocks, when all they need is ordering. This is a pity because batching is brilliant for performance when done right. This history is why I'm keen on properly explaining how to avoid deadlocks. I ran headlong into the locking issues using this cte select pattern because I had an improper understanding of the locking. One of the last things I added in my patch was the link to the MMVC doc and maybe this level of detail is unnecessary. Maybe there's a way to phrase this all that's less intimidating. The rule of thumb is use skip locked with ctid and otherwise your primary key, and then you should be fine with this pattern. To introduce these examples where your rows can change underneath you without some warning, is problematic. Those are my Christmas eve thoughts because I go eat my delicious dinner. Have a lovely holiday! Thanks, Bernice
