Hi all,
I've never submitted a patch before, sorry for any missteps. This
patch replaces ctid with an id column in the UPDATE and DELETE
self-join examples. There's an important difference I recently learned
between using ctid and a primary key column for a self-join in an
UPDATE. With a primary key, two concurrent updates will both write.
With a ctid, only one of the updates will write. Ctid is the
equivalent of using a mutable column for the self-join. Tom explains
the details much better than I can [1].
In the example in the UPDATE doc this doesn't affect the final
outcome, because it's being used to break an update into batches and
all the updates will eventually write. But imagine a system that batch
processes incoming records and uses a self-join to an ordered CTE to
prevent UPDATE deadlocks. With a primary key self-join, this will work
correctly, but with a ctid self-join, concurrent updates will very
quietly not happen. I think the example in the UPDATE doc might lead
someone to mistakenly believe that ctid can be used in a self-join to
prevent UPDATE deadlocks in the same way a primary key can be used,
and that's why I'm proposing changing it.
For reference here's why the examples were added [2]. My changes keep
all the benefits except telling users about ctid. This was the initial
focus of [2] but there's plenty of other important benefits discussed.
And perhaps ctid should not be learned about too easily.
Thanks,
Bernice
[1] https://www.postgresql.org/message-id/1645231.1763582658%40sss.pgh.pa.us
[2] https://www.postgresql.org/message-id/flat/CADkLM%3DcaNEQsUwPWnfi2jR4ix99E0EJM_3jtcE-YjnEQC7Rssw%40mail.gmail.com
