MIME-Version: 1.0
References: 
 <CAEDh4nyn5swFYuSfcnGAbpQrKOc47Hh_ZyKVSPYJcu2P=51Luw@mail.gmail.com>
 <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>
In-Reply-To: <aUwKSGHaZAd5_xKl@momjian.us>
From: Bernice Southey <bernice.southey@gmail.com>
Date: Wed, 24 Dec 2025 17:46:57 +0000
Message-ID: 
 <CAEDh4nxsqaThSkttiRdRX+sKFT1e6ymHSPPnr3szQb0OOLJTGQ@mail.gmail.com>
Subject: Re: More guidance on ctid
To: Bruce Momjian <bruce@momjian.us>
Cc: pgsql-docs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAEDh4nxsqaThSkttiRdRX%2BsKFT1e6ymHSPPnr3szQb0OOLJTGQ%40mail.gmail.com>
Precedence: bulk

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?
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