MIME-Version: 1.0
References: <19355-57d7d52ea4980dc6@postgresql.org> <868ff2a518820c8864b6d28510294b2457a126af.camel@cybertec.at>
 <CANjBVm3AMm=9-mB7=uqJjaWN-+RrA8+w-gXhZn6L7VHu7K_Urw@mail.gmail.com>
 <CAEZATCVcV4cfqGnmPGVxwjsPh7rxsTD510=KOGFyi2_FMgqWaQ@mail.gmail.com>
 <CA+HiwqE8gNK7Utqo2EAKh24KExWfypt06m1EFNXBd5TFDO2f6w@mail.gmail.com>
 <CAHewXNn8ApcYfZKjZ1R8Z7yHRoi+C+mu5r9LbZUbb6691d6Rrw@mail.gmail.com>
 <CAEZATCVr+=F=iTbi3XyABSEr5Cqw=ptm2PyGc75SaQ1JYrPnsg@mail.gmail.com> <CAHewXN=D2SUh1CvE0gnk1Ev_k4RAEO9aoEgQZG_7eAuSWxZA0Q@mail.gmail.com>
In-Reply-To: <CAHewXN=D2SUh1CvE0gnk1Ev_k4RAEO9aoEgQZG_7eAuSWxZA0Q@mail.gmail.com>
From: Bernice Southey <bernice.southey@gmail.com>
Date: Wed, 31 Dec 2025 16:16:15 +0000
Message-ID: <CAEDh4nw=h5NihhuBgq4GFCfPP9vcg54QJ+K0XsG9S0owWDUhfA@mail.gmail.com>
Subject: Re: BUG #19355: Attempt to insert data unexpectedly during concurrent update
To: Tender Wang <tndrwang@gmail.com>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>, Amit Langote <amitlangote09@gmail.com>, 
	Bh W <wangbihua.cn@gmail.com>, Laurenz Albe <laurenz.albe@cybertec.at>, 
	pgsql-bugs@lists.postgresql.org
Content-Type: multipart/mixed; boundary="000000000000cf8f1f064741cdab"
Archived-At: <https://www.postgresql.org/message-id/CAEDh4nw%3Dh5NihhuBgq4GFCfPP9vcg54QJ%2BK0XsG9S0owWDUhfA%40mail.gmail.com>
Precedence: bulk

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

Hi,

I've spent some time looking at the code and history of this bug
because I also ran into it [1] and I was deeply curious why concurrent
updates worked with a hash join + seq scan but not a nested loop +
index scan.

I was also curious if this bug is why Yuri didn't see deadlocks in
this related bug [2].

FWIW, I found a simpler way to reproduce this:

create table t(id int primary key, count int);
insert into t values (1, 0), (2, 0);

--session 1
begin;
update t set count = count + 1;

--session 2
set enable_seqscan = off;
update t set count = count + 1 from (values (1), (2)) v(id) where t.id = v.id;

Only one update succeeds in session 2.

Tender Wang wrote:
> Dean Rasheed wrote:
>> I'm somewhat conflicted as to which approach is better. I think maybe
>> there is less chance of other unintended side-effects if the set of
>> RTEs included in PlannerGlobal.allRelids, unprunableRelids, and
>> es_unpruned_relids is not changed. However, as it stands,
>> PlannerGlobal.allRelids is misnamed (it should probably have been
>> called "relationRelids", in line with the "relationOids" field).
>> Making it actually include all RTEs would solve that.

> Per the comment, I guess Amit didn't plan to include all RTEs in his design.

While digging, I noticed that allRelids seems only to be used to
create unprunableRelids. But unprunableRelids is used among other
things, to create es_unpruned_relids and as an unpruned_relids
parameter to ExecInitRangeTable. It's difficult to follow all the
paths where the missing relids will matter. So I think Tender's
approach is safer if amended to include all relids. I'm very new to
postgres source code, so this is a very tentative opinion. Amit can
best answer after his holiday.

I was curious enough to test in the meantime. I made an experimental
patch that just adds all rels to allRelids. This fixed all the issues
I'm aware of. It caused overexplain tests to fail by adding in new
non-relation relIds as expected. Apologies if I'm getting the
terminology wrong.

I ran Yuri's reproducer on my patched branch and indeed got several deadlocks.
psql:crash.sql:3: ERROR:  deadlock detected
DETAIL:  Process 71673 waits for ShareLock on transaction 2766;
blocked by process 71643.
Process 71643 waits for ShareLock on transaction 2777; blocked by process 71673.

Curiously, in the test I added (based on Dean's), enable_indexscan or
enable_seqscan didn't make any difference to the plan. But when I run
the reproducer manually, it does change the plan. Since the bug
disappears with a hash join + seq scan, the plan is critical. So I
included 'set enable_seqscan to 0' for reference and future proofing.

I didn't attempt to fix the comment because I don't understand it. I
also didn't check all my tabs vs spaces, or run TAP tests, as this
patch is just meant for info. Most of the code is still way over my
head, so my apologies if none of this is helpful.

As to why this works for a hash join but not a nested loop, I have
some ideas, but I'm still digging.

Happy New Year!
Bernice

[1] https://www.postgresql.org/message-id/flat/CAMbWs4-uC8DWRZvByHej%2B9E5em7V_%2BzNyRfFx-UUpR4HwifK4w%40mail.gmail.com
[2] https://www.postgresql.org/message-id/ly6rss443iajiv5cfypzu6fgrmxdbwzdtuujkddr4eis7s2gcq%40gcdytigety6e

--000000000000cf8f1f064741cdab
Content-Type: text/x-patch; charset="US-ASCII"; name="concurrent-update-experiment.patch"
Content-Disposition: attachment; 
	filename="concurrent-update-experiment.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mju61eez0>
X-Attachment-Id: f_mju61eez0

RnJvbSA1NDUyNjc3MzMyMWE1MjVhMGU4MDZiZjE3MzUzNTM3YTRiYzIzZmVmIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBCZXJuaWNlIFNvdXRoZXkgPGJlcm5pY2Uuc291dGhleUBnbWFp
bC5jb20+CkRhdGU6IFdlZCwgMzEgRGVjIDIwMjUgMTQ6MjY6MzkgKzAwMDAKU3ViamVjdDogW1BB
VENIXSBjb25jdXJyZW50IHVwZGF0ZSBleHBlcmltZW50CgotLS0KIC4uLi9wZ19vdmVyZXhwbGFp
bi9leHBlY3RlZC9wZ19vdmVyZXhwbGFpbi5vdXQgIHwgIDkgKysrKystLS0tCiBzcmMvYmFja2Vu
ZC9vcHRpbWl6ZXIvcGxhbi9zZXRyZWZzLmMgICAgICAgICAgICB8ICA1ICsrKy0tCiBzcmMvdGVz
dC9pc29sYXRpb24vZXhwZWN0ZWQvZXZhbC1wbGFuLXF1YWwub3V0ICB8IDE3ICsrKysrKysrKysr
KysrKysrCiBzcmMvdGVzdC9pc29sYXRpb24vc3BlY3MvZXZhbC1wbGFuLXF1YWwuc3BlYyAgICB8
IDEwICsrKysrKysrKysKIDQgZmlsZXMgY2hhbmdlZCwgMzUgaW5zZXJ0aW9ucygrKSwgNiBkZWxl
dGlvbnMoLSkKCmRpZmYgLS1naXQgYS9jb250cmliL3BnX292ZXJleHBsYWluL2V4cGVjdGVkL3Bn
X292ZXJleHBsYWluLm91dCBiL2NvbnRyaWIvcGdfb3ZlcmV4cGxhaW4vZXhwZWN0ZWQvcGdfb3Zl
cmV4cGxhaW4ub3V0CmluZGV4IDU1ZDM0NjY2ZDg3Li40NWE3MWFmNTc3NiAxMDA2NDQKLS0tIGEv
Y29udHJpYi9wZ19vdmVyZXhwbGFpbi9leHBlY3RlZC9wZ19vdmVyZXhwbGFpbi5vdXQKKysrIGIv
Y29udHJpYi9wZ19vdmVyZXhwbGFpbi9leHBlY3RlZC9wZ19vdmVyZXhwbGFpbi5vdXQKQEAgLTQ3
LDcgKzQ3LDggQEAgRVhQTEFJTiAoUkFOR0VfVEFCTEUpIFNFTEVDVCAxOwogICAgUlRJczogMQog
IFJUSSAxIChyZXN1bHQpOgogICAgRXJlZjogIipSRVNVTFQqIiAoKQotKDQgcm93cykKKyBVbnBy
dW5hYmxlIFJUSXM6IDEKKyg1IHJvd3MpCiAKIC0tIENyZWF0ZSBhIHBhcnRpdGlvbmVkIHRhYmxl
LgogQ1JFQVRFIFRBQkxFIHZlZ2V0YWJsZXMgKGlkIHNlcmlhbCwgbmFtZSB0ZXh0LCBnZW51cyB0
ZXh0KQpAQCAtMTQxLDcgKzE0Miw3IEBAICQkKTsKICAgIFJlbGF0aW9uOiBkYXVjdXMKICAgIFJl
bGF0aW9uIEtpbmQ6IHJlbGF0aW9uCiAgICBSZWxhdGlvbiBMb2NrIE1vZGU6IEFjY2Vzc1NoYXJl
TG9jawotIFVucHJ1bmFibGUgUlRJczogMSAzIDQKKyBVbnBydW5hYmxlIFJUSXM6IDEgMiAzIDQK
ICg1MyByb3dzKQogCiAtLSBUZXN0IGEgZGlmZmVyZW50IG91dHB1dCBmb3JtYXQuCkBAIC0yOTIs
NyArMjkzLDcgQEAgJCQpOwogICAgICAgICAgPFNlY3VyaXR5LUJhcnJpZXI+ZmFsc2U8L1NlY3Vy
aXR5LUJhcnJpZXI+ICAgICAgICAgICAgICAgICArCiAgICAgICAgICA8TGF0ZXJhbD5mYWxzZTwv
TGF0ZXJhbD4gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICsKICAgICAgICA8L1Jh
bmdlLVRhYmxlLUVudHJ5PiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
KwotICAgICAgIDxVbnBydW5hYmxlLVJUSXM+MSAzIDQ8L1VucHJ1bmFibGUtUlRJcz4gICAgICAg
ICAgICAgICAgICAgICArCisgICAgICAgPFVucHJ1bmFibGUtUlRJcz4xIDIgMyA0PC9VbnBydW5h
YmxlLVJUSXM+ICAgICAgICAgICAgICAgICAgICsKICAgICAgICA8UmVzdWx0LVJUSXM+bm9uZTwv
UmVzdWx0LVJUSXM+ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgKwogICAgICA8L1Jhbmdl
LVRhYmxlPiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAr
CiAgICA8L1F1ZXJ5PiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICsKQEAgLTQ4NSw3ICs0ODYsNyBAQCBJTlNFUlQgSU5UTyB2ZWdldGFibGVz
IChuYW1lLCBnZW51cykgVkFMVUVTICgnYnJvY2NvZmxvd2VyJywgJ2JyYXNzaWNhJyk7CiAgICBQ
ZXJtaXNzaW9uIEluZm8gSW5kZXg6IDEKICBSVEkgMiAocmVzdWx0KToKICAgIEVyZWY6ICIqUkVT
VUxUKiIgKCkKLSBVbnBydW5hYmxlIFJUSXM6IDEKKyBVbnBydW5hYmxlIFJUSXM6IDEgMgogIFJl
c3VsdCBSVElzOiAxCiAoMTUgcm93cykKIApkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvb3B0aW1p
emVyL3BsYW4vc2V0cmVmcy5jIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BsYW4vc2V0cmVmcy5j
CmluZGV4IGNkN2VhMWU2YjU4Li40YmZjYmY0ZWZiNSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQv
b3B0aW1pemVyL3BsYW4vc2V0cmVmcy5jCisrKyBiL3NyYy9iYWNrZW5kL29wdGltaXplci9wbGFu
L3NldHJlZnMuYwpAQCAtNTgwLDEyICs1ODAsMTMgQEAgYWRkX3J0ZV90b19mbGF0X3J0YWJsZShQ
bGFubmVyR2xvYmFsICpnbG9iLCBMaXN0ICpydGVwZXJtaW5mb3MsCiAJICogTm90ZSB3ZSBkb24n
dCBib3RoZXIgdG8gYXZvaWQgbWFraW5nIGR1cGxpY2F0ZSBsaXN0IGVudHJpZXMuICBXZSBjb3Vs
ZCwKIAkgKiBidXQgaXQgd291bGQgcHJvYmFibHkgY29zdCBtb3JlIGN5Y2xlcyB0aGFuIGl0IHdv
dWxkIHNhdmUuCiAJICovCisJZ2xvYi0+YWxsUmVsaWRzID0gYm1zX2FkZF9tZW1iZXIoZ2xvYi0+
YWxsUmVsaWRzLAorCQkJCQkJCQkJCSBsaXN0X2xlbmd0aChnbG9iLT5maW5hbHJ0YWJsZSkpOwkK
KwkKIAlpZiAobmV3cnRlLT5ydGVraW5kID09IFJURV9SRUxBVElPTiB8fAogCQkobmV3cnRlLT5y
dGVraW5kID09IFJURV9TVUJRVUVSWSAmJiBPaWRJc1ZhbGlkKG5ld3J0ZS0+cmVsaWQpKSkKIAl7
CiAJCWdsb2ItPnJlbGF0aW9uT2lkcyA9IGxhcHBlbmRfb2lkKGdsb2ItPnJlbGF0aW9uT2lkcywg
bmV3cnRlLT5yZWxpZCk7Ci0JCWdsb2ItPmFsbFJlbGlkcyA9IGJtc19hZGRfbWVtYmVyKGdsb2It
PmFsbFJlbGlkcywKLQkJCQkJCQkJCQkgbGlzdF9sZW5ndGgoZ2xvYi0+ZmluYWxydGFibGUpKTsK
IAl9CiAKIAkvKgpkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvaXNvbGF0aW9uL2V4cGVjdGVkL2V2YWwt
cGxhbi1xdWFsLm91dCBiL3NyYy90ZXN0L2lzb2xhdGlvbi9leHBlY3RlZC9ldmFsLXBsYW4tcXVh
bC5vdXQKaW5kZXggMDVmZmZlMGQ1NzAuLmQxODJiYmUyNzIyIDEwMDY0NAotLS0gYS9zcmMvdGVz
dC9pc29sYXRpb24vZXhwZWN0ZWQvZXZhbC1wbGFuLXF1YWwub3V0CisrKyBiL3NyYy90ZXN0L2lz
b2xhdGlvbi9leHBlY3RlZC9ldmFsLXBsYW4tcXVhbC5vdXQKQEAgLTE0MzIsNiArMTQzMiwyMyBA
QCBhfGJ8Y3wgICBkCiAoMiByb3dzKQogCiAKK3N0YXJ0aW5nIHBlcm11dGF0aW9uOiB1cGRhdGVm
b3J2YWx1ZXMgdXBkYXRldmFsdWVzIGMxIGMyIHJlYWQKK3N0ZXAgdXBkYXRlZm9ydmFsdWVzOiBV
UERBVEUgYWNjb3VudHMgU0VUIGJhbGFuY2UgPSBiYWxhbmNlICsgMTAwOworc3RlcCB1cGRhdGV2
YWx1ZXM6IAorCXNldCBsb2NhbCBlbmFibGVfc2Vxc2NhbiB0byAwOworCVVQREFURSBhY2NvdW50
cyBTRVQgYmFsYW5jZSA9IHYuYmFsYW5jZSBGUk9NIChWQUxVRVMoJ2NoZWNraW5nJywgNjEwKSwg
KCdzYXZpbmdzJywgNjIwKSkgdihhY2NvdW50aWQsIGJhbGFuY2UpIFdIRVJFIGFjY291bnRzLmFj
Y291bnRpZCA9IHYuYWNjb3VudGlkOworIDx3YWl0aW5nIC4uLj4KK3N0ZXAgYzE6IENPTU1JVDsK
K3N0ZXAgdXBkYXRldmFsdWVzOiA8Li4uIGNvbXBsZXRlZD4KK3N0ZXAgYzI6IENPTU1JVDsKK3N0
ZXAgcmVhZDogU0VMRUNUICogRlJPTSBhY2NvdW50cyBPUkRFUiBCWSBhY2NvdW50aWQ7CithY2Nv
dW50aWR8YmFsYW5jZXxiYWxhbmNlMgorLS0tLS0tLS0tKy0tLS0tLS0rLS0tLS0tLS0KK2NoZWNr
aW5nIHwgICAgNjEwfCAgICAxMjIwCitzYXZpbmdzICB8ICAgIDYyMHwgICAgMTI0MAorKDIgcm93
cykKKworCiBzdGFydGluZyBwZXJtdXRhdGlvbjogc3lzMSBzeXN1cGQyIGMxIGMyCiBzdGVwIHN5
czE6IAogCVVQREFURSBwZ19jbGFzcyBTRVQgcmVsdHVwbGVzID0gMTIzIFdIRVJFIG9pZCA9ICdh
Y2NvdW50cyc6OnJlZ2NsYXNzOwpkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvaXNvbGF0aW9uL3NwZWNz
L2V2YWwtcGxhbi1xdWFsLnNwZWMgYi9zcmMvdGVzdC9pc29sYXRpb24vc3BlY3MvZXZhbC1wbGFu
LXF1YWwuc3BlYwppbmRleCA4MGUxZTZiYjMwNy4uNDg3MDY5MGFjNWIgMTAwNjQ0Ci0tLSBhL3Ny
Yy90ZXN0L2lzb2xhdGlvbi9zcGVjcy9ldmFsLXBsYW4tcXVhbC5zcGVjCisrKyBiL3NyYy90ZXN0
L2lzb2xhdGlvbi9zcGVjcy9ldmFsLXBsYW4tcXVhbC5zcGVjCkBAIC0yMDYsNiArMjA2LDggQEAg
c3RlcCBzeXMxCXsKIAogc3RlcCBzMXBwMSB7IFVQREFURSBhbm90aGVyX3BhcnR0YmwgU0VUIGIg
PSBiICsgMSBXSEVSRSBhID0gMTsgfQogCitzdGVwIHVwZGF0ZWZvcnZhbHVlcyB7IFVQREFURSBh
Y2NvdW50cyBTRVQgYmFsYW5jZSA9IGJhbGFuY2UgKyAxMDA7IH0KKwogc2Vzc2lvbiBzMgogc2V0
dXAJCXsgQkVHSU4gSVNPTEFUSU9OIExFVkVMIFJFQUQgQ09NTUlUVEVEOyB9CiBzdGVwIHd4Mgl7
IFVQREFURSBhY2NvdW50cyBTRVQgYmFsYW5jZSA9IGJhbGFuY2UgKyA0NTAgV0hFUkUgYWNjb3Vu
dGlkID0gJ2NoZWNraW5nJyBSRVRVUk5JTkcgYmFsYW5jZTsgfQpAQCAtMzE4LDYgKzMyMCwxMSBA
QCBzdGVwIHMycHAyIHsgUFJFUEFSRSBlcGQgQVMgREVMRVRFIEZST00gYW5vdGhlcl9wYXJ0dGJs
IFdIRVJFIGEgPSAkMTsgfQogc3RlcCBzMnBwMyB7IEVYRUNVVEUgZXBkKDEpOyB9CiBzdGVwIHMy
cHA0IHsgREVMRVRFIEZST00gYW5vdGhlcl9wYXJ0dGJsIFdIRVJFIGEgPSAoU0VMRUNUIDEpOyB9
CiAKK3N0ZXAgdXBkYXRldmFsdWVzIHsgCisJc2V0IGxvY2FsIGVuYWJsZV9zZXFzY2FuIHRvIDA7
CisJVVBEQVRFIGFjY291bnRzIFNFVCBiYWxhbmNlID0gdi5iYWxhbmNlIEZST00gKFZBTFVFUygn
Y2hlY2tpbmcnLCA2MTApLCAoJ3NhdmluZ3MnLCA2MjApKSB2KGFjY291bnRpZCwgYmFsYW5jZSkg
V0hFUkUgYWNjb3VudHMuYWNjb3VudGlkID0gdi5hY2NvdW50aWQ7Cit9IAorCiBzZXNzaW9uIHMz
CiBzZXR1cAkJeyBCRUdJTiBJU09MQVRJT04gTEVWRUwgUkVBRCBDT01NSVRURUQ7IH0KIHN0ZXAg
cmVhZAl7IFNFTEVDVCAqIEZST00gYWNjb3VudHMgT1JERVIgQlkgYWNjb3VudGlkOyB9CkBAIC00
MTksNiArNDI2LDkgQEAgcGVybXV0YXRpb24gc2ltcGxlcGFydHVwZGF0ZV9yb3V0ZTF0bzIgY29t
cGxleHBhcnR1cGRhdGVfcm91dGVfZXJyMSBjMSBjMiByZWFkX3AKIHBlcm11dGF0aW9uIHNpbXBs
ZXBhcnR1cGRhdGVfbm9yb3V0ZSBjb21wbGV4cGFydHVwZGF0ZV9yb3V0ZSBjMSBjMiByZWFkX3Bh
cnQKIHBlcm11dGF0aW9uIHNpbXBsZXBhcnR1cGRhdGVfbm9yb3V0ZSBjb21wbGV4cGFydHVwZGF0
ZV9kb2VzbnRfcm91dGUgYzEgYzIgcmVhZF9wYXJ0CiAKKyMgdGVzdCBFUFEgcmVjaGVjayBpbiBV
UERBVEUgZnJvbSBWQUxVRVNfUlRFLCBjZiBidWcgIzE5MzU1CitwZXJtdXRhdGlvbiB1cGRhdGVm
b3J2YWx1ZXMgdXBkYXRldmFsdWVzIGMxIGMyIHJlYWQKKwogcGVybXV0YXRpb24gc3lzMSBzeXN1
cGQyIGMxIGMyCiBwZXJtdXRhdGlvbiBzeXMxIHN5c21lcmdlMiBjMSBjMgogCi0tIAoyLjQzLjAK
Cg==
--000000000000cf8f1f064741cdab--