MIME-Version: 1.0
References: 
 <CAEDh4nyn5swFYuSfcnGAbpQrKOc47Hh_ZyKVSPYJcu2P=51Luw@mail.gmail.com>
 <aSTL3rgmztLq1UIH@momjian.us>
In-Reply-To: <aSTL3rgmztLq1UIH@momjian.us>
From: Bernice Southey <bernice.southey@gmail.com>
Date: Sat, 29 Nov 2025 22:45:45 +0000
Message-ID: 
 <CAEDh4nzDMoRDZMDGGxKHzJ5hZscmvT2FWioTzQ6A0aJVtCO8NQ@mail.gmail.com>
Subject: Re: More guidance on ctid
To: Bruce Momjian <bruce@momjian.us>
Cc: pgsql-docs@lists.postgresql.org
Content-Type: multipart/mixed; boundary="000000000000e039150644c38353"
Archived-At: 
 <https://www.postgresql.org/message-id/CAEDh4nzDMoRDZMDGGxKHzJ5hZscmvT2FWioTzQ6A0aJVtCO8NQ%40mail.gmail.com>
Precedence: bulk

--000000000000e039150644c38353
Content-Type: text/plain; charset="UTF-8"

Bruce Momjian <bruce@momjian.us> wrote:
> I think the reporter above extrapolated this example to use ctid in
> other, non-appropriate cases.  Should we add a warning to the docs to
> explain that the general use of ctid is discouraged?

I've been explaining this badly. Here's an example that I hope will
make clear why I'm so concerned.

(Read Committed default isolation level)
drop table if exists b1, b2;
create table b1 as select 1 id, 100 balance;
create table b2 as select 1 id, 100 balance;

With an immutable column self-join, it works as expected and
serializes the balance changes.

--session1
begin;
with x as (select id from b1 order by id for update)
update b1 set balance = balance + 1 from x where b1.id = x.id;

--session2
with x as (select id from b1 order by id for update)
update b1 set balance = balance - 100 from x where b1.id = x.id;
select * from b1;

--session1
commit;

--session2
UPDATE 1
 id | balance
----+---------
  1 |       1
(1 row)

But with ctid, the second update is lost.

--session1
begin;
with x as (select ctid from b2 order by id for update)
update b2 set balance = balance + 1 from x where b2.ctid = x.ctid;

--session2
with x as (select ctid from b2 order by id for update)
update b2 set balance = balance - 100 from x where b2.ctid = x.ctid;
select * from b2;

--session1
commit;

--session2
UPDATE 0
 id | balance
----+---------
  1 |     101
(1 row)

The session2 CTE blocks until it gets the updated version of the row
from session 1 commit. It gets b2.ctid = (0,2) giving x.ctid = (0,2).
But the UPDATE gets b2.ctid = (0,1) at the start of the transaction.
This doesn't change even after session 1 commits. The join fails. This
is correct, but not obvious.

This is why I think the docs should say something different to what
they currently do. The UPDATE example is fine only because it's run
repeatedly until there are no more rows to find.

I learned the primary key self-join pattern for order by/limit in
delete/update soon after I began with postgres. I'm pretty sure ctid
would have confused me. So I think the doc examples with an id column
will be very helpful, and safer.

If users discover ctid on internet forums or LLMs and then check the
docs, I think they should get some notice of the concurrency risk.

Here's a patch with an attempt to do the above.

Forum examples:
https://stackoverflow.com/questions/10245560/deadlocks-in-postgresql-when-running-update/71163671#71163671
https://www.reddit.com/r/PostgreSQL/comments/1cn0q1c/comment/l36ppve/

Thanks, Bernice

--000000000000e039150644c38353
Content-Type: text/x-patch; charset="US-ASCII";
	name="v1-add-ctid-guidance-and-remove-from-examples.patch"
Content-Disposition: attachment;
	filename="v1-add-ctid-guidance-and-remove-from-examples.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_miktz81l0>
X-Attachment-Id: f_miktz81l0

RnJvbSA2OTU5NWFlYjlmMWU2ZWRhNzRmYTI5ZmRlMjJiZDExN2E4MDQ0ODEyIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBCZXJuaWNlIFNvdXRoZXkgPGJlcm5pY2Uuc291dGhleUBnbWFp
bC5jb20+CkRhdGU6IFRodSwgMjAgTm92IDIwMjUgMTE6NTM6NTcgKzAwMDAKU3ViamVjdDogW1BB
VENIXSBBZGQgY3RpZCBndWlkYW5jZSBhbmQgcmVtb3ZlIGZyb20gZXhhbXBsZXMKCi0tLQogZG9j
L3NyYy9zZ21sL2RkbC5zZ21sICAgICAgICB8ICA1ICsrKystCiBkb2Mvc3JjL3NnbWwvcmVmL2Rl
bGV0ZS5zZ21sIHwgMTAgKysrKystLS0tLQogZG9jL3NyYy9zZ21sL3JlZi91cGRhdGUuc2dtbCB8
ICA5ICsrKy0tLS0tLQogMyBmaWxlcyBjaGFuZ2VkLCAxMiBpbnNlcnRpb25zKCspLCAxMiBkZWxl
dGlvbnMoLSkKCmRpZmYgLS1naXQgYS9kb2Mvc3JjL3NnbWwvZGRsLnNnbWwgYi9kb2Mvc3JjL3Nn
bWwvZGRsLnNnbWwKaW5kZXggMDlhZDg0MDBmZDAuLjk5YzhmYWNmZTBiIDEwMDY0NAotLS0gYS9k
b2Mvc3JjL3NnbWwvZGRsLnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL2RkbC5zZ21sCkBAIC0xNTU5
LDcgKzE1NTksMTAgQEAgQ1JFQVRFIFRBQkxFIGNpcmNsZXMgKAogICAgICAgPHN0cnVjdGZpZWxk
PmN0aWQ8L3N0cnVjdGZpZWxkPiB3aWxsIGNoYW5nZSBpZiBpdCBpcwogICAgICAgdXBkYXRlZCBv
ciBtb3ZlZCBieSA8Y29tbWFuZD5WQUNVVU0gRlVMTDwvY29tbWFuZD4uICBUaGVyZWZvcmUKICAg
ICAgIDxzdHJ1Y3RmaWVsZD5jdGlkPC9zdHJ1Y3RmaWVsZD4gaXMgdXNlbGVzcyBhcyBhIGxvbmct
dGVybSByb3cKLSAgICAgIGlkZW50aWZpZXIuICBBIHByaW1hcnkga2V5IHNob3VsZCBiZSB1c2Vk
IHRvIGlkZW50aWZ5IGxvZ2ljYWwgcm93cy4KKyAgICAgIGlkZW50aWZpZXIuICBCZSBhd2FyZSB0
aGF0IGN0aWRzIHdpbGwgY2hhbmdlIGluIGRhdGEtbW9kaWZ5aW5nCisgICAgICBjb25jdXJyZW50
IHRyYW5zYWN0aW9ucywgYW5kIHRoZXkgYXJlIG5vdCB1bmlxdWUgYWNyb3NzIHRhYmxlIHBhcnRp
dGlvbnMuCisgICAgICBBIHByaW1hcnkga2V5IHNob3VsZCBiZSB1c2VkIHRvIGlkZW50aWZ5IGxv
Z2ljYWwgcm93cy4KKyAgICAgIAogICAgICA8L3BhcmE+CiAgICAgPC9saXN0aXRlbT4KICAgIDwv
dmFybGlzdGVudHJ5PgpkaWZmIC0tZ2l0IGEvZG9jL3NyYy9zZ21sL3JlZi9kZWxldGUuc2dtbCBi
L2RvYy9zcmMvc2dtbC9yZWYvZGVsZXRlLnNnbWwKaW5kZXggNWI1MmY3N2UyOGYuLjQ4ZWIxYjY3
NTVhIDEwMDY0NAotLS0gYS9kb2Mvc3JjL3NnbWwvcmVmL2RlbGV0ZS5zZ21sCisrKyBiL2RvYy9z
cmMvc2dtbC9yZWYvZGVsZXRlLnNnbWwKQEAgLTMxMywxNSArMzEzLDE1IEBAIERFTEVURSBGUk9N
IHRhc2tzIFdIRVJFIENVUlJFTlQgT0YgY190YXNrczsKICAgIGRvY3VtZW50YXRpb24gb2YgPGNv
bW1hbmQ+VVBEQVRFPC9jb21tYW5kPjwvbGluaz46CiA8cHJvZ3JhbWxpc3Rpbmc+CiBXSVRIIGRl
bGV0ZV9iYXRjaCBBUyAoCi0gIFNFTEVDVCBsLmN0aWQgRlJPTSB1c2VyX2xvZ3MgQVMgbAotICAg
IFdIRVJFIGwuc3RhdHVzID0gJ2FyY2hpdmVkJwotICAgIE9SREVSIEJZIGwuY3JlYXRpb25fZGF0
ZQorICBTRUxFQ1QgYS5pZCBGUk9NIGFjY291bnRzIEFTIGEKKyAgICBXSEVSRSBhLnN0YXR1cyA9
ICdhcmNoaXZlZCcKKyAgICBPUkRFUiBCWSBhLmlkCiAgICAgRk9SIFVQREFURQogICAgIExJTUlU
IDEwMDAwCiApCi1ERUxFVEUgRlJPTSB1c2VyX2xvZ3MgQVMgZGwKK0RFTEVURSBGUk9NIGFjY291
bnRzIEFTIGRsCiAgIFVTSU5HIGRlbGV0ZV9iYXRjaCBBUyBkZWwKLSAgV0hFUkUgZGwuY3RpZCA9
IGRlbC5jdGlkOworICBXSEVSRSBkbC5pZCA9IGRlbC5pZDsKIDwvcHJvZ3JhbWxpc3Rpbmc+CiAg
IDwvcGFyYT4KICA8L3JlZnNlY3QxPgpkaWZmIC0tZ2l0IGEvZG9jL3NyYy9zZ21sL3JlZi91cGRh
dGUuc2dtbCBiL2RvYy9zcmMvc2dtbC9yZWYvdXBkYXRlLnNnbWwKaW5kZXggNDBjY2EwNjM5NDYu
LjczNzRiODg0MDgwIDEwMDY0NAotLS0gYS9kb2Mvc3JjL3NnbWwvcmVmL3VwZGF0ZS5zZ21sCisr
KyBiL2RvYy9zcmMvc2dtbC9yZWYvdXBkYXRlLnNnbWwKQEAgLTQ4NiwxMyArNDg2LDEwIEBAIFVQ
REFURSBmaWxtcyBTRVQga2luZCA9ICdEcmFtYXRpYycgV0hFUkUgQ1VSUkVOVCBPRiBjX2ZpbG1z
OwogICAgbm8gPGxpdGVyYWw+TElNSVQ8L2xpdGVyYWw+IGNsYXVzZSBmb3IgPGNvbW1hbmQ+VVBE
QVRFPC9jb21tYW5kPiwgaXQgaXMKICAgIHBvc3NpYmxlIHRvIGdldCBhIHNpbWlsYXIgZWZmZWN0
IHRocm91Z2ggdGhlIHVzZSBvZgogICAgYSA8bGluayBsaW5rZW5kPSJxdWVyaWVzLXdpdGgiPkNv
bW1vbiBUYWJsZSBFeHByZXNzaW9uPC9saW5rPiBhbmQgYQotICAgc2VsZi1qb2luLiAgV2l0aCB0
aGUgc3RhbmRhcmQgPHByb2R1Y3RuYW1lPlBvc3RncmVTUUw8L3Byb2R1Y3RuYW1lPgotICAgdGFi
bGUgYWNjZXNzIG1ldGhvZCwgYSBzZWxmLWpvaW4gb24gdGhlIHN5c3RlbQotICAgY29sdW1uIDxs
aW5rIGxpbmtlbmQ9ImRkbC1zeXN0ZW0tY29sdW1ucy1jdGlkIj5jdGlkPC9saW5rPiBpcyB2ZXJ5
Ci0gICBlZmZpY2llbnQ6CisgICBzZWxmLWpvaW46CiA8cHJvZ3JhbWxpc3Rpbmc+CiBXSVRIIGV4
Y2VlZGVkX21heF9yZXRyaWVzIEFTICgKLSAgU0VMRUNUIHcuY3RpZCBGUk9NIHdvcmtfaXRlbSBB
UyB3CisgIFNFTEVDVCB3LmlkIEZST00gd29ya19pdGVtIEFTIHcKICAgICBXSEVSRSB3LnN0YXR1
cyA9ICdhY3RpdmUnIEFORCB3Lm51bV9yZXRyaWVzICZndDsgMTAKICAgICBPUkRFUiBCWSB3LnJl
dHJ5X3RpbWVzdGFtcAogICAgIEZPUiBVUERBVEUKQEAgLTUwMCw3ICs0OTcsNyBAQCBXSVRIIGV4
Y2VlZGVkX21heF9yZXRyaWVzIEFTICgKICkKIFVQREFURSB3b3JrX2l0ZW0gU0VUIHN0YXR1cyA9
ICdmYWlsZWQnCiAgIEZST00gZXhjZWVkZWRfbWF4X3JldHJpZXMgQVMgZW1yCi0gIFdIRVJFIHdv
cmtfaXRlbS5jdGlkID0gZW1yLmN0aWQ7CisgIFdIRVJFIHdvcmtfaXRlbS5pZCA9IGVtci5pZDsK
IDwvcHJvZ3JhbWxpc3Rpbmc+CiAgICBUaGlzIGNvbW1hbmQgd2lsbCBuZWVkIHRvIGJlIHJlcGVh
dGVkIHVudGlsIG5vIHJvd3MgcmVtYWluIHRvIGJlIHVwZGF0ZWQuCiAgICBVc2Ugb2YgYW4gPGxp
dGVyYWw+T1JERVIgQlk8L2xpdGVyYWw+IGNsYXVzZSBhbGxvd3MgdGhlIGNvbW1hbmQgdG8KLS0g
CjIuNDMuMAoK
--000000000000e039150644c38353--