MIME-Version: 1.0
References: <ec498c3d-5f2b-48ec-b989-5561c8aa2024@illuminatedcomputing.com>
 <85ac7f0e-d95f-4377-ade0-8941fd328012@eisentraut.org>
 <CA+renyUazgR-hB_6RY60n23L0y-n_h9G1AappZmPENO0k5pL1g@mail.gmail.com>
 <7d63ddfa-c735-4dfe-8c7a-4f1e2a621058@eisentraut.org>
 <CA+renyVXg5pV84wQnGQuK8-=qoKw3BiBgQzesxM_LkcxxWmYjA@mail.gmail.com>
 <e59d218a-ae49-490f-ae6f-86c12f35d3c3@eisentraut.org>
 <CA+renyWKOj5=rMmQmJcbybu-Vdomxdp=eJ93kp76AgmQKYdfiQ@mail.gmail.com>
 <4606deaa-7d65-4f22-8a78-356c3180be9d@eisentraut.org>
 <CA+renyUhuXB2nTVCMREXew9E4DZOnFxQNjME5bcw91+k72Bosg@mail.gmail.com>
 <CA+renyWUCSyTMn3s03kviEN-oaVrJP-QkDQCLNfaY=MHV5QEiQ@mail.gmail.com>
 <CA+renyV4tWU2d=n9_v=XNPHbZfNqqLokzd-Xt78M-zLd+46ubA@mail.gmail.com>
 <53f1c094-3c29-4ef6-a9bd-dc2e7894ceb0@eisentraut.org>
 <CA+renyUSgqXpjj+vV7w+wirPB49VQFrmPjVT_s04JmZSOPNNsQ@mail.gmail.com>
 <b2ecbf9f-797f-433d-953f-3c848cde5a1c@eisentraut.org>
 <CA+renyX-eV+2hFUaZg3BSREqLE7dh+LoWm7ZqhFAiGsirjjtRQ@mail.gmail.com>
 <e2fc32a4-319b-4176-8bd8-1d974e31376f@eisentraut.org>
 <CAHg+QDckLFqthQyox2NDetYRs9sRrjmAiSA-gYRowyg8w_4vgw@mail.gmail.com>
 <CAHg+QDd74fnd4obCRMqVS0AVWf=cSFH=Cv7trTJWgm+_bhTK6w@mail.gmail.com>
 <CAHg+QDcVL2d4ih5zs2Mzh63ts41N+jtnMQTdZ2_0be6aF4aqYQ@mail.gmail.com>
 <CACJufxEaD_DtByjv4CZg3yKg0n6hMPThfOmTr-D6JKY+v1BJDQ@mail.gmail.com>
 <CA+renyW9o=-DBK6YO7a31xbr6xw7RFN_tSxTiUi2tSOQ2w5_zQ@mail.gmail.com>
In-Reply-To: 
 <CA+renyW9o=-DBK6YO7a31xbr6xw7RFN_tSxTiUi2tSOQ2w5_zQ@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Tue, 21 Apr 2026 14:25:06 +0800
Message-ID: 
 <CACJufxGtDyUNUnz7BnCWGdS4s1BErhbmD04wuAUKC-U-XSF36A@mail.gmail.com>
Subject: Re: SQL:2011 Application Time Update & Delete
To: Paul A Jungwirth <pj@illuminatedcomputing.com>
Cc: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>,
 Peter Eisentraut <peter@eisentraut.org>,
	Chao Li <li.evan.chao@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="00000000000028fc7b064ff27c3e"
Archived-At: 
 <https://www.postgresql.org/message-id/CACJufxGtDyUNUnz7BnCWGdS4s1BErhbmD04wuAUKC-U-XSF36A%40mail.gmail.com>
Precedence: bulk

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

On Thu, Apr 16, 2026 at 7:26=E2=80=AFAM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:
>
> I think using INSTEAD OF triggers to replace an UPDATE/DELETE FOR
> PORTION OF is a valid use-case, but it doesn't make sense to insert
> temporal leftovers. As you say, we can't access the underlying
> storage. But also we don't know what changes the trigger actually
> made. The trigger should be responsible for leftovers, and we
> shouldn't try to add more. So I think the fix is just to skip
> inserting leftovers. I've attached a patch to do that.
>
hi.

CREATE TABLE fpo_instead_base (id int, valid_at daterange, val int);
INSERT INTO fpo_instead_base VALUES (1, '[2024-01-01,2024-12-31)', 100);
CREATE VIEW fpo_instead_view AS SELECT * FROM fpo_instead_base;
CREATE FUNCTION fpo_instead_trig_fn() RETURNS trigger LANGUAGE plpgsql AS $=
$
BEGIN
    RETURN NEW;
END;
$$;
CREATE TRIGGER fpo_instead_trig INSTEAD OF UPDATE ON fpo_instead_view
  FOR EACH ROW EXECUTE FUNCTION fpo_instead_trig_fn();

UPDATE fpo_instead_view FOR PORTION OF valid_at FROM '2024-04-01' TO
'2024-08-01'
    SET val =3D 999 WHERE id =3D 1
    RETURNING *;

 id |        valid_at         | val
----+-------------------------+-----
  1 | [2024-01-01,2024-12-31) | 999
(1 row)

Should I expect the column `valid_at` value as [2024-04-01,2024-08-01) ?

We should also document this on doc/src/sgml/ref/update.sgml
Attached is a minor regession test enhancement for
"v2-0001-Fix-INSTEAD-OF-triggers-with-DELETE-UPDATE-FOR-PO.patch".


--
jian
https://www.enterprisedb.com/

--00000000000028fc7b064ff27c3e
Content-Type: application/octet-stream;
	name="v3-0001-misc-fix-for-V2-Fix-INSTEAD-OF-triggers-with-DELETE-UPDATE-FOR.no-cfbot"
Content-Disposition: attachment;
	filename="v3-0001-misc-fix-for-V2-Fix-INSTEAD-OF-triggers-with-DELETE-UPDATE-FOR.no-cfbot"
Content-Transfer-Encoding: base64
Content-ID: <f_mo88o0pi0>
X-Attachment-Id: f_mo88o0pi0

RnJvbSBkMmMzNjdmOTVjOWYzNzU4YjMxZTE0YzEyM2JkODZlZGZiMDNmYzdlIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBqaWFuIGhlIDxqaWFuLnVuaXZlcnNhbGl0eUBnbWFpbC5jb20+
CkRhdGU6IFR1ZSwgMjEgQXByIDIwMjYgMTQ6MjE6MDEgKzA4MDAKU3ViamVjdDogW1BBVENIIHYz
IDEvMV0gbWlzYyBmaXggZm9yCiBWMi1GaXgtSU5TVEVBRC1PRi10cmlnZ2Vycy13aXRoLURFTEVU
RS1VUERBVEUtRk9SLVBPUlRJT04tT0YKCkRpc2N1c3Npb246IGh0dHBzOi8vcG9zdGdyLmVzL20v
Q0FIZyUyQlFEZDc0Zm5kNG9iQ1JNcVZTMEFWV2YlM0RjU0ZIJTNEQ3Y3dHJUSldnbSUyQl9iaFRL
NnclNDBtYWlsLmdtYWlsLmNvbQpEaXNjdXNzaW9uOiBodHRwczovL3Bvc3Rnci5lcy9tL0NBK3Jl
bnlXOW89LURCSzZZTzdhMzF4YnI2eHc3UkZOX3RTeFRpVWkydFNPUTJ3NV96UUBtYWlsLmdtYWls
LmNvbQotLS0KIHNyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvZm9yX3BvcnRpb25fb2Yub3V0IHwg
MTYgKysrKysrKysrKysrKy0tLQogc3JjL3Rlc3QvcmVncmVzcy9zcWwvZm9yX3BvcnRpb25fb2Yu
c3FsICAgICAgfCAxNCArKysrKysrKysrKy0tLQogMiBmaWxlcyBjaGFuZ2VkLCAyNCBpbnNlcnRp
b25zKCspLCA2IGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhw
ZWN0ZWQvZm9yX3BvcnRpb25fb2Yub3V0IGIvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9mb3Jf
cG9ydGlvbl9vZi5vdXQKaW5kZXggMWFmYTI2Yzg2YmMuLjQ3NzE5ZmM0MGVhIDEwMDY0NAotLS0g
YS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2Zvcl9wb3J0aW9uX29mLm91dAorKysgYi9zcmMv
dGVzdC9yZWdyZXNzL2V4cGVjdGVkL2Zvcl9wb3J0aW9uX29mLm91dApAQCAtMjEwMywxNSArMjEw
MywyNCBAQCBJTlNFUlQgSU5UTyBmcG9faW5zdGVhZF9iYXNlIFZBTFVFUyAoMSwgJ1syMDI0LTAx
LTAxLDIwMjQtMTItMzEpJywgMTAwKTsKIENSRUFURSBWSUVXIGZwb19pbnN0ZWFkX3ZpZXcgQVMg
U0VMRUNUICogRlJPTSBmcG9faW5zdGVhZF9iYXNlOwogQ1JFQVRFIEZVTkNUSU9OIGZwb19pbnN0
ZWFkX3RyaWdfZm4oKSBSRVRVUk5TIHRyaWdnZXIgTEFOR1VBR0UgcGxwZ3NxbCBBUyAkJAogQkVH
SU4KKyAgICAgIGlmIFRHX09QID0gJ1VQREFURScgdGhlbgorICAgICAgICAgIHJhaXNlIE5PVElD
RSAnVVBEQVRFIE9MRDogJSwgTkVXOiAlJywgT0xELCBORVc7CisgICAgICAgICAgUkVUVVJOIE5F
VzsKKyAgICAgIGVsc2lmIFRHX09QID0gJ0lOU0VSVCcgdGhlbgorICAgICAgICAgIHJhaXNlIE5P
VElDRSAnSU5TRVJUIE5FVzogJScsIE5FVzsKKyAgICAgICAgICBSRVRVUk4gTkVXOworICAgICAg
ZWxzaWYgVEdfT1AgPSAnREVMRVRFJyB0aGVuCisgICAgICAgICAgcmFpc2UgTk9USUNFICdERUxF
VEU6IE9MRDogJScsIE9MRDsKKyAgICAgICAgICBSRVRVUk4gT0xEOworICAgICAgZW5kIGlmOwog
ICAgIFJFVFVSTiBORVc7CiBFTkQ7CiAkJDsKLUNSRUFURSBUUklHR0VSIGZwb19pbnN0ZWFkX3Ry
aWcgSU5TVEVBRCBPRiBVUERBVEUgT04gZnBvX2luc3RlYWRfdmlldwotICBGT1IgRUFDSCBST1cg
RVhFQ1VURSBGVU5DVElPTiBmcG9faW5zdGVhZF90cmlnX2ZuKCk7Ci1DUkVBVEUgVFJJR0dFUiBm
cG9faW5zdGVhZF9kZWxfdHJpZyBJTlNURUFEIE9GIERFTEVURSBPTiBmcG9faW5zdGVhZF92aWV3
CitDUkVBVEUgVFJJR0dFUiBmcG9faW5zdGVhZF90cmlnIElOU1RFQUQgT0YgVVBEQVRFIE9SIERF
TEVURSBPTiBmcG9faW5zdGVhZF92aWV3CiAgIEZPUiBFQUNIIFJPVyBFWEVDVVRFIEZVTkNUSU9O
IGZwb19pbnN0ZWFkX3RyaWdfZm4oKTsKIFVQREFURSBmcG9faW5zdGVhZF92aWV3IEZPUiBQT1JU
SU9OIE9GIHZhbGlkX2F0IEZST00gJzIwMjQtMDQtMDEnIFRPICcyMDI0LTA4LTAxJwogICAgIFNF
VCB2YWwgPSA5OTkgV0hFUkUgaWQgPSAxOworTk9USUNFOiAgVVBEQVRFIE9MRDogKDEsIlsyMDI0
LTAxLTAxLDIwMjQtMTItMzEpIiwxMDApLCBORVc6ICgxLCJbMjAyNC0wMS0wMSwyMDI0LTEyLTMx
KSIsOTk5KQogU0VMRUNUICogRlJPTSBmcG9faW5zdGVhZF92aWV3OwogIGlkIHwgICAgICAgIHZh
bGlkX2F0ICAgICAgICAgfCB2YWwgCiAtLS0tKy0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0rLS0t
LS0KQEAgLTIxMjAsNiArMjEyOSw3IEBAIFNFTEVDVCAqIEZST00gZnBvX2luc3RlYWRfdmlldzsK
IAogREVMRVRFIEZST00gZnBvX2luc3RlYWRfdmlldyBGT1IgUE9SVElPTiBPRiB2YWxpZF9hdCBG
Uk9NICcyMDI0LTA0LTAxJyBUTyAnMjAyNC0wOC0wMScKICAgICBXSEVSRSBpZCA9IDE7CitOT1RJ
Q0U6ICBERUxFVEU6IE9MRDogKDEsIlsyMDI0LTAxLTAxLDIwMjQtMTItMzEpIiwxMDApCiBTRUxF
Q1QgKiBGUk9NIGZwb19pbnN0ZWFkX3ZpZXc7CiAgaWQgfCAgICAgICAgdmFsaWRfYXQgICAgICAg
ICB8IHZhbCAKIC0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLQpkaWZmIC0tZ2l0
IGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvZm9yX3BvcnRpb25fb2Yuc3FsIGIvc3JjL3Rlc3QvcmVn
cmVzcy9zcWwvZm9yX3BvcnRpb25fb2Yuc3FsCmluZGV4IDBiNWE4NjQwOGI5Li44MzA4MmY4NzM5
YSAxMDA2NDQKLS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvZm9yX3BvcnRpb25fb2Yuc3FsCisr
KyBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2Zvcl9wb3J0aW9uX29mLnNxbApAQCAtMTM3MSwxMiAr
MTM3MSwyMCBAQCBJTlNFUlQgSU5UTyBmcG9faW5zdGVhZF9iYXNlIFZBTFVFUyAoMSwgJ1syMDI0
LTAxLTAxLDIwMjQtMTItMzEpJywgMTAwKTsKIENSRUFURSBWSUVXIGZwb19pbnN0ZWFkX3ZpZXcg
QVMgU0VMRUNUICogRlJPTSBmcG9faW5zdGVhZF9iYXNlOwogQ1JFQVRFIEZVTkNUSU9OIGZwb19p
bnN0ZWFkX3RyaWdfZm4oKSBSRVRVUk5TIHRyaWdnZXIgTEFOR1VBR0UgcGxwZ3NxbCBBUyAkJAog
QkVHSU4KKyAgICAgIGlmIFRHX09QID0gJ1VQREFURScgdGhlbgorICAgICAgICAgIHJhaXNlIE5P
VElDRSAnVVBEQVRFIE9MRDogJSwgTkVXOiAlJywgT0xELCBORVc7CisgICAgICAgICAgUkVUVVJO
IE5FVzsKKyAgICAgIGVsc2lmIFRHX09QID0gJ0lOU0VSVCcgdGhlbgorICAgICAgICAgIHJhaXNl
IE5PVElDRSAnSU5TRVJUIE5FVzogJScsIE5FVzsKKyAgICAgICAgICBSRVRVUk4gTkVXOworICAg
ICAgZWxzaWYgVEdfT1AgPSAnREVMRVRFJyB0aGVuCisgICAgICAgICAgcmFpc2UgTk9USUNFICdE
RUxFVEU6IE9MRDogJScsIE9MRDsKKyAgICAgICAgICBSRVRVUk4gT0xEOworICAgICAgZW5kIGlm
OwogICAgIFJFVFVSTiBORVc7CiBFTkQ7CiAkJDsKLUNSRUFURSBUUklHR0VSIGZwb19pbnN0ZWFk
X3RyaWcgSU5TVEVBRCBPRiBVUERBVEUgT04gZnBvX2luc3RlYWRfdmlldwotICBGT1IgRUFDSCBS
T1cgRVhFQ1VURSBGVU5DVElPTiBmcG9faW5zdGVhZF90cmlnX2ZuKCk7Ci1DUkVBVEUgVFJJR0dF
UiBmcG9faW5zdGVhZF9kZWxfdHJpZyBJTlNURUFEIE9GIERFTEVURSBPTiBmcG9faW5zdGVhZF92
aWV3CitDUkVBVEUgVFJJR0dFUiBmcG9faW5zdGVhZF90cmlnIElOU1RFQUQgT0YgVVBEQVRFIE9S
IERFTEVURSBPTiBmcG9faW5zdGVhZF92aWV3CiAgIEZPUiBFQUNIIFJPVyBFWEVDVVRFIEZVTkNU
SU9OIGZwb19pbnN0ZWFkX3RyaWdfZm4oKTsKIAogVVBEQVRFIGZwb19pbnN0ZWFkX3ZpZXcgRk9S
IFBPUlRJT04gT0YgdmFsaWRfYXQgRlJPTSAnMjAyNC0wNC0wMScgVE8gJzIwMjQtMDgtMDEnCi0t
IAoyLjM0LjEKCg==
--00000000000028fc7b064ff27c3e--