MIME-Version: 1.0
From: Charles Qi <qyqgpower@gmail.com>
Date: Wed, 6 Aug 2025 20:37:49 +0800
Message-ID: <CAEawgcJN2V0MTzZu5gi=csNapnJzogxJJqUFSphu6r=OXjorng@mail.gmail.com>
Subject: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX
 of new tuple is set to current XID
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/mixed; boundary="0000000000007e20bd063bb19c44"
Archived-At: <https://www.postgresql.org/message-id/CAEawgcJN2V0MTzZu5gi%3DcsNapnJzogxJJqUFSphu6r%3DOXjorng%40mail.gmail.com>
Precedence: bulk

--0000000000007e20bd063bb19c44
Content-Type: multipart/alternative; boundary="0000000000007e20bd063bb19c42"

--0000000000007e20bd063bb19c42
Content-Type: text/plain; charset="UTF-8"

And if we do the updates in multiple subtransactions, multixact will be
created, which is not created when the BEFORE ROW UPDATE trigger is absent.

Is this behavior by design? If so, what is the purpose for the behavior?

Tested version:
PostgreSQL 14.18 (Ubuntu 14.18-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

The attached file reproduce.sql can be used to reproduce the behavior.

--0000000000007e20bd063bb19c42
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">And if we do the updates in multiple subtransactions, mult=
ixact will be created, which is not created when the BEFORE ROW UPDATE trig=
ger is absent.<div><br>Is this behavior by design? If so, what is the purpo=
se for the behavior?<div><br><div>Tested version:</div><div>PostgreSQL 14.1=
8 (Ubuntu 14.18-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (=
Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit</div><div><br></div><div>The a=
ttached file=C2=A0reproduce.sql can be used to reproduce the behavior.=C2=
=A0</div></div></div></div>

--0000000000007e20bd063bb19c42--
--0000000000007e20bd063bb19c44
Content-Type: application/octet-stream; name="reproduce.sql"
Content-Disposition: attachment; filename="reproduce.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_mdzy6i1d0>
X-Attachment-Id: f_mdzy6i1d0

LS1DUkVBVEUgRVhURU5TSU9OIHBhZ2VpbnNwZWN0Ow0KY3JlYXRlIHRhYmxlIHRfdGVzdCAoaWQg
aW50LCBjb250ZW50IHRleHQpOw0KaW5zZXJ0IGludG8gdF90ZXN0IHZhbHVlcyAoMSwgJ3Rlc3Qn
KTsNCnVwZGF0ZSB0X3Rlc3Qgc2V0IGNvbnRlbnQ9J3Rlc3QxJyB3aGVyZSBpZD0xOw0KDQotLXNx
bCBmb3IgaW5zcGVjdCB0aGUgcGFnZQ0KU0VMRUNUIHRfeG1pbiwgdF94bWF4LCB0X2N0aWQsIHJh
d19mbGFncywgY29tYmluZWRfZmxhZ3MgRlJPTSBoZWFwX3BhZ2VfaXRlbXMoZ2V0X3Jhd19wYWdl
KCd0X3Rlc3QnLCAwKSksIA0KTEFURVJBTCBoZWFwX3R1cGxlX2luZm9tYXNrX2ZsYWdzKHRfaW5m
b21hc2ssIHRfaW5mb21hc2syKQ0KV0hFUkUgdF9pbmZvbWFzayBJUyBOT1QgTlVMTCBPUiB0X2lu
Zm9tYXNrMiBJUyBOT1QgTlVMTDsNCg0KLS14bWF4IG9uIHRoZSBuZXcgdHVwbGUgaXMgMCBhcyBl
eHBlY3RlZA0KIHRfeG1pbiB8IHRfeG1heCB8IHRfY3RpZCB8ICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICByYXdfZmxhZ3MgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwgY29tYmluZWRf
ZmxhZ3MNCi0tLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0t
LS0tLQ0KICAgIDc1MSB8ICAgIDc1MiB8ICgwLDIpICB8IHtIRUFQX0hBU1ZBUldJRFRILEhFQVBf
WE1JTl9DT01NSVRURUQsSEVBUF9IT1RfVVBEQVRFRH0gICAgICAgICAgIHwge30NCiAgICA3NTIg
fCAgICAgIDAgfCAoMCwyKSAgfCB7SEVBUF9IQVNWQVJXSURUSCxIRUFQX1hNQVhfSU5WQUxJRCxI
RUFQX1VQREFURUQsSEVBUF9PTkxZX1RVUExFfSB8IHt9DQooMiByb3dzKQ0KDQotLWNyZWF0ZSBh
IGJlZm9yZSByb3cgdXBkYXRlIHRyaWdnZXIgb24gdF90ZXN0DQpDUkVBVEUgT1IgUkVQTEFDRSBG
VU5DVElPTiBiZWZvcmVfdF90ZXN0KCkNClJFVFVSTlMgVFJJR0dFUiBBUyAkJA0KQkVHSU4NCiAg
ICBSRVRVUk4gTkVXOw0KRU5EOw0KJCQgTEFOR1VBR0UgcGxwZ3NxbDsNCg0KQ1JFQVRFIFRSSUdH
RVIgdHJfYmVmb3JlX3RfdGVzdF91cGRhdGUNCkJFRk9SRSBVUERBVEUgT04gdF90ZXN0DQpGT1Ig
RUFDSCBST1cNCkVYRUNVVEUgRlVOQ1RJT04gYmVmb3JlX3RfdGVzdCgpOw0KDQotLXVwZGF0ZSB0
aGUgc2FtZSByb3cgYWdhaW4NCnVwZGF0ZSB0X3Rlc3Qgc2V0IGNvbnRlbnQ9J3Rlc3QyJyB3aGVy
ZSBpZD0xOw0KDQotLXRoZSB4bWF4IGluIHRoZSBuZXcgdHVwbGUgaXMgZXF1YWwgdG8geG1pbg0K
LS1hbmQgSEVBUF9YTUFYX0tFWVNIUl9MT0NLIGlzIHNldA0KIHRfeG1pbiB8IHRfeG1heCB8IHRf
Y3RpZCB8ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICByYXdfZmxhZ3Mg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwgY29tYmluZWRfZmxhZ3MN
Ci0tLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0rLS0tLS0tLS0tLS0tLS0tLQ0KICAgIDc1MSB8ICAgIDc1MiB8ICgwLDIpICB8IHtIRUFQ
X0hBU1ZBUldJRFRILEhFQVBfWE1JTl9DT01NSVRURUQsSEVBUF9YTUFYX0NPTU1JVFRFRCxIRUFQ
X0hPVF9VUERBVEVEfSAgICAgICAgICAgICAgIHwge30NCiAgICA3NTIgfCAgICA3NTUgfCAoMCwz
KSAgfCB7SEVBUF9IQVNWQVJXSURUSCxIRUFQX1hNSU5fQ09NTUlUVEVELEhFQVBfVVBEQVRFRCxI
RUFQX0hPVF9VUERBVEVELEhFQVBfT05MWV9UVVBMRX0gICAgICB8IHt9DQogICAgNzU1IHwgICAg
NzU1IHwgKDAsMykgIHwge0hFQVBfSEFTVkFSV0lEVEgsSEVBUF9YTUFYX0tFWVNIUl9MT0NLLEhF
QVBfWE1BWF9MT0NLX09OTFksSEVBUF9VUERBVEVELEhFQVBfT05MWV9UVVBMRX0gfCB7fQ0KKDMg
cm93cykNCg0KLS11cGRhdGUgdGhlIHNhbWUgcm93IHdpdGggdHdvIHN1YnRyYW5zYWN0aW9ucw0K
YmVnaW47DQpzYXZlcG9pbnQgc3AxOw0KdXBkYXRlIHRfdGVzdCBzZXQgY29udGVudD0ndGVzdDMn
IHdoZXJlIGlkPTE7DQpzYXZlcG9pbnQgc3AyOw0KdXBkYXRlIHRfdGVzdCBzZXQgY29udGVudD0n
dGVzdDQnIHdoZXJlIGlkPTE7DQpjb21taXQ7DQoNCi0tdHdvIG5ldyB0dXBsZXMgYXJlIGluc2Vy
dGVkIHdpdGggeG1heCBzZXQgdG8gbXVsdGl4YWN0IGlkDQotLWFuZCBIRUFQX1hNQVhfRVhDTF9M
T0NLIGlzIHNldA0KIHRfeG1pbiB8IHRfeG1heCB8IHRfY3RpZCB8ICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHJhd19mbGFncyAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICB8IGNvbWJpbmVkX2Zs
YWdzDQotLS0tLS0tLSstLS0tLS0tLSstLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tDQog
ICAgNzUxIHwgICAgNzUyIHwgKDAsMikgIHwge0hFQVBfSEFTVkFSV0lEVEgsSEVBUF9YTUlOX0NP
TU1JVFRFRCxIRUFQX1hNQVhfQ09NTUlUVEVELEhFQVBfSE9UX1VQREFURUR9ICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwge30NCiAgICA3NTIgfCAgICA3NTUgfCAo
MCwzKSAgfCB7SEVBUF9IQVNWQVJXSURUSCxIRUFQX1hNSU5fQ09NTUlUVEVELEhFQVBfWE1BWF9D
T01NSVRURUQsSEVBUF9VUERBVEVELEhFQVBfSE9UX1VQREFURUQsSEVBUF9PTkxZX1RVUExFfSAg
ICAgICAgICAgICAgfCB7fQ0KICAgIDc1NSB8ICAgIDc1NyB8ICgwLDQpICB8IHtIRUFQX0hBU1ZB
UldJRFRILEhFQVBfWE1JTl9DT01NSVRURUQsSEVBUF9VUERBVEVELEhFQVBfSE9UX1VQREFURUQs
SEVBUF9PTkxZX1RVUExFfSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICB8IHt9DQog
ICAgNzU3IHwgICAgICA0IHwgKDAsNSkgIHwge0hFQVBfSEFTVkFSV0lEVEgsSEVBUF9DT01CT0NJ
RCxIRUFQX1hNQVhfRVhDTF9MT0NLLEhFQVBfWE1BWF9JU19NVUxUSSxIRUFQX1VQREFURUQsSEVB
UF9IT1RfVVBEQVRFRCxIRUFQX09OTFlfVFVQTEV9IHwge30NCiAgICA3NTggfCAgICAgIDMgfCAo
MCw1KSAgfCB7SEVBUF9IQVNWQVJXSURUSCxIRUFQX1hNQVhfRVhDTF9MT0NLLEhFQVBfWE1BWF9M
T0NLX09OTFksSEVBUF9YTUFYX0lTX01VTFRJLEhFQVBfVVBEQVRFRCxIRUFQX09OTFlfVFVQTEV9
ICAgICAgICAgICAgfCB7fQ0KKDUgcm93cyk=
--0000000000007e20bd063bb19c44--