MIME-Version: 1.0
References: <CAEawgcJN2V0MTzZu5gi=csNapnJzogxJJqUFSphu6r=OXjorng@mail.gmail.com>
 <1f99e5c1-a203-4441-aa5c-33a3baaf852c@aklaver.com>
In-Reply-To: <1f99e5c1-a203-4441-aa5c-33a3baaf852c@aklaver.com>
From: Charles Qi <qyqgpower@gmail.com>
Date: Fri, 8 Aug 2025 09:20:00 +0800
Message-ID: <CAEawgcLdD3-4Co8mrR7UaXJLGgBwSecUs-1QxyY3VoJzN9bnmg@mail.gmail.com>
Subject: Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the
 XMAX of new tuple is set to current XID
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/mixed; boundary="000000000000264d68063bd06033"
Archived-At: <https://www.postgresql.org/message-id/CAEawgcLdD3-4Co8mrR7UaXJLGgBwSecUs-1QxyY3VoJzN9bnmg%40mail.gmail.com>
Precedence: bulk

--000000000000264d68063bd06033
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

As I stated before, when the BEFORE ROW UPDATE trigger is absent, even
if we update the same row in multiple subtransactions inside one top
transaction, no multixact will be created.
Check the attached no_multi.sql for example.

Let me clarify the question, when the BEFORE ROW UPDATE trigger is presente=
d
Q. Why do we need to set the XMAX of the new tuple to the current xid?
which risks piling up multixacts quickly in savepoint/exception block
scenarios.


On Thu, Aug 7, 2025 at 2:22=E2=80=AFAM Adrian Klaver <adrian.klaver@aklaver=
.com> wrote:
>
> On 8/6/25 05:37, Charles Qi wrote:
> > And if we do the updates in multiple subtransactions, multixact will be
> > created, which is not created when the BEFORE ROW UPDATE trigger is abs=
ent.
> >
> > Is this behavior by design? If so, what is the purpose for the behavior=
?
>
> I would say this is by design. My reasoning is that the savepoints are
> essentially rollback points and the state of the tuple would need to be
> saved for each potential rollback. Hence a different transaction id for
> each savepoint.
>
> >
> > 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.
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

--000000000000264d68063bd06033
Content-Type: application/octet-stream; name="no_multi.sql"
Content-Disposition: attachment; filename="no_multi.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_me23zhe50>
X-Attachment-Id: f_me23zhe50

cG9zdGdyZXM9IyBjcmVhdGUgdGFibGUgdF90ZXN0IChpZCBpbnQsIGNvbnRlbnQgdGV4dCk7DQpp
bnNlcnQgaW50byB0X3Rlc3QgdmFsdWVzICgxLCAndGVzdCcpOw0KQ1JFQVRFIFRBQkxFDQpJTlNF
UlQgMCAxDQpwb3N0Z3Jlcz0jDQpwb3N0Z3Jlcz0jDQpwb3N0Z3Jlcz0jIFNFTEVDVCB0X3htaW4s
IHRfeG1heCwgdF9jdGlkLCByYXdfZmxhZ3MsIGNvbWJpbmVkX2ZsYWdzIEZST00gaGVhcF9wYWdl
X2l0ZW1zKGdldF9yYXdfcGFnZSgndF90ZXN0JywgMCkpLA0KTEFURVJBTCBoZWFwX3R1cGxlX2lu
Zm9tYXNrX2ZsYWdzKHRfaW5mb21hc2ssIHRfaW5mb21hc2syKQ0KV0hFUkUgdF9pbmZvbWFzayBJ
UyBOT1QgTlVMTCBPUiB0X2luZm9tYXNrMiBJUyBOT1QgTlVMTDsNCiB0X3htaW4gfCB0X3htYXgg
fCB0X2N0aWQgfCAgICAgICAgICAgICAgcmF3X2ZsYWdzICAgICAgICAgICAgICAgfCBjb21iaW5l
ZF9mbGFncw0KLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLQ0KICAgIDc2MSB8ICAgICAgMCB8ICgw
LDEpICB8IHtIRUFQX0hBU1ZBUldJRFRILEhFQVBfWE1BWF9JTlZBTElEfSB8IHt9DQooMSByb3cp
DQoNCnBvc3RncmVzPSMgYmVnaW47DQpzYXZlcG9pbnQgc3AxOw0KdXBkYXRlIHRfdGVzdCBzZXQg
Y29udGVudD0ndGVzdDMnIHdoZXJlIGlkPTE7DQpzYXZlcG9pbnQgc3AyOw0KdXBkYXRlIHRfdGVz
dCBzZXQgY29udGVudD0ndGVzdDQnIHdoZXJlIGlkPTE7DQpjb21taXQ7DQpCRUdJTg0KU0FWRVBP
SU5UDQpVUERBVEUgMQ0KU0FWRVBPSU5UDQpVUERBVEUgMQ0KQ09NTUlUDQpwb3N0Z3Jlcz0jIFNF
TEVDVCB0X3htaW4sIHRfeG1heCwgdF9jdGlkLCByYXdfZmxhZ3MsIGNvbWJpbmVkX2ZsYWdzIEZS
T00gaGVhcF9wYWdlX2l0ZW1zKGdldF9yYXdfcGFnZSgndF90ZXN0JywgMCkpLA0KTEFURVJBTCBo
ZWFwX3R1cGxlX2luZm9tYXNrX2ZsYWdzKHRfaW5mb21hc2ssIHRfaW5mb21hc2syKQ0KV0hFUkUg
dF9pbmZvbWFzayBJUyBOT1QgTlVMTCBPUiB0X2luZm9tYXNrMiBJUyBOT1QgTlVMTDsNCiB0X3ht
aW4gfCB0X3htYXggfCB0X2N0aWQgfCAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
cmF3X2ZsYWdzICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgfCBjb21iaW5lZF9m
bGFncw0KLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0r
LS0tLS0tLS0tLS0tLS0tLQ0KICAgIDc2MSB8ICAgIDc2MyB8ICgwLDIpICB8IHtIRUFQX0hBU1ZB
UldJRFRILEhFQVBfWE1JTl9DT01NSVRURUQsSEVBUF9IT1RfVVBEQVRFRH0gICAgICAgICAgICAg
ICAgICAgICAgICB8IHt9DQogICAgNzYzIHwgICAgNzY0IHwgKDAsMykgIHwge0hFQVBfSEFTVkFS
V0lEVEgsSEVBUF9DT01CT0NJRCxIRUFQX1VQREFURUQsSEVBUF9IT1RfVVBEQVRFRCxIRUFQX09O
TFlfVFVQTEV9IHwge30NCiAgICA3NjQgfCAgICAgIDAgfCAoMCwzKSAgfCB7SEVBUF9IQVNWQVJX
SURUSCxIRUFQX1hNQVhfSU5WQUxJRCxIRUFQX1VQREFURUQsSEVBUF9PTkxZX1RVUExFfSAgICAg
ICAgICAgICAgfCB7fQ0KKDMgcm93cyk=
--000000000000264d68063bd06033--