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>
In-Reply-To: 
 <CACJufxEaD_DtByjv4CZg3yKg0n6hMPThfOmTr-D6JKY+v1BJDQ@mail.gmail.com>
From: Paul A Jungwirth <pj@illuminatedcomputing.com>
Date: Wed, 15 Apr 2026 16:25:58 -0700
Message-ID: 
 <CA+renyW9o=-DBK6YO7a31xbr6xw7RFN_tSxTiUi2tSOQ2w5_zQ@mail.gmail.com>
Subject: Re: SQL:2011 Application Time Update & Delete
To: jian he <jian.universality@gmail.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="00000000000076fc3c064f880a6c"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BrenyW9o%3D-DBK6YO7a31xbr6xw7RFN_tSxTiUi2tSOQ2w5_zQ%40mail.gmail.com>
Precedence: bulk

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

On Mon, Apr 13, 2026 at 9:33=E2=80=AFPM jian he <jian.universality@gmail.co=
m> wrote:
>
> On Fri, Apr 10, 2026 at 3:42=E2=80=AFAM SATYANARAYANA NARLAPURAM
> <satyanarlapuram@gmail.com> wrote:
> >
> >> Repro:
> >>
> >> CREATE TABLE t (id INT, valid_at daterange, val INT);
> >> INSERT INTO t VALUES (1, '[2026-01-01,2026-12-31)', 100);
> >> CREATE VIEW v AS SELECT * FROM t;
> >>
> >> CREATE FUNCTION v_trig() RETURNS trigger LANGUAGE plpgsql AS $$
> >> BEGIN
> >>     UPDATE t SET val =3D NEW.val WHERE id =3D OLD.id;
> >>     RETURN NEW;
> >> END;
> >> $$;
> >> CREATE TRIGGER trg INSTEAD OF UPDATE ON v
> >>     FOR EACH ROW EXECUTE FUNCTION v_trig();
> >>
> >> -- This crashes the server:
> >> UPDATE v FOR PORTION OF valid_at FROM '2026-04-01' TO '2026-08-01'
> >>     SET val =3D 999 WHERE id =3D 1;
> >>
> >> I am thinking we should just reject this case. Attached a draft patch =
to fix the issue.
> >
> Yech, we should reject it.

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.

This is a good use-case for a pending followup patch (which will have
to wait for v20 I think), which makes the FOR PORTION OF parameters
accessible to triggers. We need that ourselves for PERIOD foreign keys
with CASCADE/SET NULL/SET DEFAULT, but it's nice to have another
example of why you might want it.

Yours,

--=20
Paul              ~{:-)
pj@illuminatedcomputing.com

--00000000000076fc3c064f880a6c
Content-Type: application/octet-stream;
	name="v2-0001-Fix-INSTEAD-OF-triggers-with-DELETE-UPDATE-FOR-PO.patch"
Content-Disposition: attachment;
	filename="v2-0001-Fix-INSTEAD-OF-triggers-with-DELETE-UPDATE-FOR-PO.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mo0of3t80>
X-Attachment-Id: f_mo0of3t80

RnJvbSA4ZDQzNjI1MjllZTc2OWM0OTc5NTJhNDkzOTkwOWNmYmJlODQ1M2QwIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBqaWFuIGhlIDxqaWFuLnVuaXZlcnNhbGl0eUBnbWFpbC5jb20+
CkRhdGU6IFR1ZSwgMTQgQXByIDIwMjYgMTI6MTA6MDkgKzA4MDAKU3ViamVjdDogW1BBVENIIHYy
XSBGaXggSU5TVEVBRCBPRiB0cmlnZ2VycyB3aXRoIERFTEVURS9VUERBVEUgRk9SIFBPUlRJT04g
T0YKCldlIHNob3VsZCBub3QgdHJ5IHRvIGluc2VydCB0ZW1wb3JhbCBsZWZ0b3ZlcnMgZm9sbG93
aW5nIGFuIElOU1RFQUQgT0YgdHJpZ2dlci4KRm9yIG9uZSB0aGluZywgdGhlIHJlc3VsdFJlbCBp
cyB0aGUgdmlldywgbm90IHRoZSBiYXNlIHJlbGF0aW9uLCBzbyB3ZSBjYW4ndApsb29rIHVwIHRo
ZSBwcmUtdXBkYXRlL2RlbGV0ZSByb3cuIEJ1dCBhbHNvLCB0aGUgSU5TVEVBRCBPRiB0cmlnZ2Vy
IGlzCnJlc3BvbnNpYmxlIGZvciBkb2luZyB0aGUgd29yaywgYW5kIHdlIGRvbid0IGtub3cgd2hh
dCBpdCByZWFsbHkgZGlkLiBJZiBpdAp3YW50cyBsZWZ0b3ZlcnMsIGl0IHNob3VsZCBpbnNlcnQg
dGhlbSBvciB1c2UgRk9SIFBPUlRJT04gT0YgaXRzZWxmLgoKRGlzY3Vzc2lvbjogaHR0cHM6Ly9w
b3N0Z3IuZXMvbS9DQUhnJTJCUURkNzRmbmQ0b2JDUk1xVlMwQVZXZiUzRGNTRkglM0RDdjd0clRK
V2dtJTJCX2JoVEs2dyU0MG1haWwuZ21haWwuY29tCi0tLQogc3JjL2JhY2tlbmQvZXhlY3V0b3Iv
bm9kZU1vZGlmeVRhYmxlLmMgICAgICAgfCAyMCArKysrKysrKysrKy0tCiBzcmMvdGVzdC9yZWdy
ZXNzL2V4cGVjdGVkL2Zvcl9wb3J0aW9uX29mLm91dCB8IDMxICsrKysrKysrKysrKysrKysrKysr
CiBzcmMvdGVzdC9yZWdyZXNzL3NxbC9mb3JfcG9ydGlvbl9vZi5zcWwgICAgICB8IDI1ICsrKysr
KysrKysrKysrKysKIDMgZmlsZXMgY2hhbmdlZCwgNzQgaW5zZXJ0aW9ucygrKSwgMiBkZWxldGlv
bnMoLSkKCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9leGVjdXRvci9ub2RlTW9kaWZ5VGFibGUu
YyBiL3NyYy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVNb2RpZnlUYWJsZS5jCmluZGV4IGVmMmE2YmM2
ZTlkLi5jNzc4NGNjODk2ZSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvZXhlY3V0b3Ivbm9kZU1v
ZGlmeVRhYmxlLmMKKysrIGIvc3JjL2JhY2tlbmQvZXhlY3V0b3Ivbm9kZU1vZGlmeVRhYmxlLmMK
QEAgLTE4MTAsNyArMTgxMCwxNSBAQCBFeGVjRGVsZXRlRXBpbG9ndWUoTW9kaWZ5VGFibGVDb250
ZXh0ICpjb250ZXh0LCBSZXN1bHRSZWxJbmZvICpyZXN1bHRSZWxJbmZvLAogCiAJLyogQ29tcHV0
ZSB0ZW1wb3JhbCBsZWZ0b3ZlcnMgaW4gRk9SIFBPUlRJT04gT0YgKi8KIAlpZiAoKChNb2RpZnlU
YWJsZSAqKSBjb250ZXh0LT5tdHN0YXRlLT5wcy5wbGFuKS0+Zm9yUG9ydGlvbk9mKQotCQlFeGVj
Rm9yUG9ydGlvbk9mTGVmdG92ZXJzKGNvbnRleHQsIGVzdGF0ZSwgcmVzdWx0UmVsSW5mbywgdHVw
bGVpZCk7CisJeworCQkvKgorCQkgKiBTa2lwIGxlZnRvdmVycyBpZiB0aGVyZSB3ZXJlIElOU1RF
QUQgT0YgdHJpZ2dlcnMuCisJCSAqIFdlIHdvdWxkIGhhdmUgbm8gd2F5IG9mIGFjY2Vzc2luZyB0
aGUgb2xkIHJvdy4KKwkJICovCisJCWlmICghcmVzdWx0UmVsSW5mby0+cmlfVHJpZ0Rlc2MgfHwK
KwkJCSFyZXN1bHRSZWxJbmZvLT5yaV9UcmlnRGVzYy0+dHJpZ19kZWxldGVfaW5zdGVhZF9yb3cp
CisJCQkJRXhlY0ZvclBvcnRpb25PZkxlZnRvdmVycyhjb250ZXh0LCBlc3RhdGUsIHJlc3VsdFJl
bEluZm8sIHR1cGxlaWQpOworCX0KIAogCS8qIEFGVEVSIFJPVyBERUxFVEUgVHJpZ2dlcnMgKi8K
IAlFeGVjQVJEZWxldGVUcmlnZ2Vycyhlc3RhdGUsIHJlc3VsdFJlbEluZm8sIHR1cGxlaWQsIG9s
ZHR1cGxlLApAQCAtMjYxNSw3ICsyNjIzLDE1IEBAIEV4ZWNVcGRhdGVFcGlsb2d1ZShNb2RpZnlU
YWJsZUNvbnRleHQgKmNvbnRleHQsIFVwZGF0ZUNvbnRleHQgKnVwZGF0ZUN4dCwKIAogCS8qIENv
bXB1dGUgdGVtcG9yYWwgbGVmdG92ZXJzIGluIEZPUiBQT1JUSU9OIE9GICovCiAJaWYgKCgoTW9k
aWZ5VGFibGUgKikgY29udGV4dC0+bXRzdGF0ZS0+cHMucGxhbiktPmZvclBvcnRpb25PZikKLQkJ
RXhlY0ZvclBvcnRpb25PZkxlZnRvdmVycyhjb250ZXh0LCBjb250ZXh0LT5lc3RhdGUsIHJlc3Vs
dFJlbEluZm8sIHR1cGxlaWQpOworCXsKKwkJLyoKKwkJICogU2tpcCBsZWZ0b3ZlcnMgaWYgdGhl
cmUgd2VyZSBJTlNURUFEIE9GIHRyaWdnZXJzLgorCQkgKiBXZSB3b3VsZCBoYXZlIG5vIHdheSBv
ZiBhY2Nlc3NpbmcgdGhlIG9sZCByb3cuCisJCSAqLworCQlpZiAoIXJlc3VsdFJlbEluZm8tPnJp
X1RyaWdEZXNjIHx8CisJCQkhcmVzdWx0UmVsSW5mby0+cmlfVHJpZ0Rlc2MtPnRyaWdfZGVsZXRl
X2luc3RlYWRfcm93KQorCQkJRXhlY0ZvclBvcnRpb25PZkxlZnRvdmVycyhjb250ZXh0LCBjb250
ZXh0LT5lc3RhdGUsIHJlc3VsdFJlbEluZm8sIHR1cGxlaWQpOworCX0KIAogCS8qIEFGVEVSIFJP
VyBVUERBVEUgVHJpZ2dlcnMgKi8KIAlFeGVjQVJVcGRhdGVUcmlnZ2Vycyhjb250ZXh0LT5lc3Rh
dGUsIHJlc3VsdFJlbEluZm8sCmRpZmYgLS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVk
L2Zvcl9wb3J0aW9uX29mLm91dCBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvZm9yX3BvcnRp
b25fb2Yub3V0CmluZGV4IDMxZjc3MmM3MjNkLi4xYWZhMjZjODZiYyAxMDA2NDQKLS0tIGEvc3Jj
L3Rlc3QvcmVncmVzcy9leHBlY3RlZC9mb3JfcG9ydGlvbl9vZi5vdXQKKysrIGIvc3JjL3Rlc3Qv
cmVncmVzcy9leHBlY3RlZC9mb3JfcG9ydGlvbl9vZi5vdXQKQEAgLTIwOTcsNCArMjA5NywzNSBA
QCBTRUxFQ1QgKiBGUk9NIHRlbXBvcmFsX3BhcnRpdGlvbmVkXzUgT1JERVIgQlkgaWQsIHZhbGlk
X2F0OwogKDQgcm93cykKIAogRFJPUCBUQUJMRSB0ZW1wb3JhbF9wYXJ0aXRpb25lZDsKKy0tIFRl
c3Q6IEZPUiBQT1JUSU9OIE9GIHNob3VsZCBiZSByZWplY3RlZCBvbiB2aWV3cyB3aXRoIElOU1RF
QUQgT0YgdHJpZ2dlcnMKK0NSRUFURSBUQUJMRSBmcG9faW5zdGVhZF9iYXNlIChpZCBpbnQsIHZh
bGlkX2F0IGRhdGVyYW5nZSwgdmFsIGludCk7CitJTlNFUlQgSU5UTyBmcG9faW5zdGVhZF9iYXNl
IFZBTFVFUyAoMSwgJ1syMDI0LTAxLTAxLDIwMjQtMTItMzEpJywgMTAwKTsKK0NSRUFURSBWSUVX
IGZwb19pbnN0ZWFkX3ZpZXcgQVMgU0VMRUNUICogRlJPTSBmcG9faW5zdGVhZF9iYXNlOworQ1JF
QVRFIEZVTkNUSU9OIGZwb19pbnN0ZWFkX3RyaWdfZm4oKSBSRVRVUk5TIHRyaWdnZXIgTEFOR1VB
R0UgcGxwZ3NxbCBBUyAkJAorQkVHSU4KKyAgICBSRVRVUk4gTkVXOworRU5EOworJCQ7CitDUkVB
VEUgVFJJR0dFUiBmcG9faW5zdGVhZF90cmlnIElOU1RFQUQgT0YgVVBEQVRFIE9OIGZwb19pbnN0
ZWFkX3ZpZXcKKyAgRk9SIEVBQ0ggUk9XIEVYRUNVVEUgRlVOQ1RJT04gZnBvX2luc3RlYWRfdHJp
Z19mbigpOworQ1JFQVRFIFRSSUdHRVIgZnBvX2luc3RlYWRfZGVsX3RyaWcgSU5TVEVBRCBPRiBE
RUxFVEUgT04gZnBvX2luc3RlYWRfdmlldworICBGT1IgRUFDSCBST1cgRVhFQ1VURSBGVU5DVElP
TiBmcG9faW5zdGVhZF90cmlnX2ZuKCk7CitVUERBVEUgZnBvX2luc3RlYWRfdmlldyBGT1IgUE9S
VElPTiBPRiB2YWxpZF9hdCBGUk9NICcyMDI0LTA0LTAxJyBUTyAnMjAyNC0wOC0wMScKKyAgICBT
RVQgdmFsID0gOTk5IFdIRVJFIGlkID0gMTsKK1NFTEVDVCAqIEZST00gZnBvX2luc3RlYWRfdmll
dzsKKyBpZCB8ICAgICAgICB2YWxpZF9hdCAgICAgICAgIHwgdmFsIAorLS0tLSstLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tKy0tLS0tCisgIDEgfCBbMjAyNC0wMS0wMSwyMDI0LTEyLTMxKSB8IDEw
MAorKDEgcm93KQorCitERUxFVEUgRlJPTSBmcG9faW5zdGVhZF92aWV3IEZPUiBQT1JUSU9OIE9G
IHZhbGlkX2F0IEZST00gJzIwMjQtMDQtMDEnIFRPICcyMDI0LTA4LTAxJworICAgIFdIRVJFIGlk
ID0gMTsKK1NFTEVDVCAqIEZST00gZnBvX2luc3RlYWRfdmlldzsKKyBpZCB8ICAgICAgICB2YWxp
ZF9hdCAgICAgICAgIHwgdmFsIAorLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tKy0tLS0t
CisgIDEgfCBbMjAyNC0wMS0wMSwyMDI0LTEyLTMxKSB8IDEwMAorKDEgcm93KQorCitEUk9QIFZJ
RVcgZnBvX2luc3RlYWRfdmlldzsKK0RST1AgVEFCTEUgZnBvX2luc3RlYWRfYmFzZTsKIFJFU0VU
IGRhdGVzdHlsZTsKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2Zvcl9wb3J0aW9u
X29mLnNxbCBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2Zvcl9wb3J0aW9uX29mLnNxbAppbmRleCBk
NDA2MmFjZjFkMS4uMGI1YTg2NDA4YjkgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3Fs
L2Zvcl9wb3J0aW9uX29mLnNxbAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9mb3JfcG9ydGlv
bl9vZi5zcWwKQEAgLTEzNjUsNCArMTM2NSwyOSBAQCBTRUxFQ1QgKiBGUk9NIHRlbXBvcmFsX3Bh
cnRpdGlvbmVkXzUgT1JERVIgQlkgaWQsIHZhbGlkX2F0OwogCiBEUk9QIFRBQkxFIHRlbXBvcmFs
X3BhcnRpdGlvbmVkOwogCistLSBUZXN0OiBGT1IgUE9SVElPTiBPRiBzaG91bGQgYmUgcmVqZWN0
ZWQgb24gdmlld3Mgd2l0aCBJTlNURUFEIE9GIHRyaWdnZXJzCitDUkVBVEUgVEFCTEUgZnBvX2lu
c3RlYWRfYmFzZSAoaWQgaW50LCB2YWxpZF9hdCBkYXRlcmFuZ2UsIHZhbCBpbnQpOworSU5TRVJU
IElOVE8gZnBvX2luc3RlYWRfYmFzZSBWQUxVRVMgKDEsICdbMjAyNC0wMS0wMSwyMDI0LTEyLTMx
KScsIDEwMCk7CitDUkVBVEUgVklFVyBmcG9faW5zdGVhZF92aWV3IEFTIFNFTEVDVCAqIEZST00g
ZnBvX2luc3RlYWRfYmFzZTsKK0NSRUFURSBGVU5DVElPTiBmcG9faW5zdGVhZF90cmlnX2ZuKCkg
UkVUVVJOUyB0cmlnZ2VyIExBTkdVQUdFIHBscGdzcWwgQVMgJCQKK0JFR0lOCisgICAgUkVUVVJO
IE5FVzsKK0VORDsKKyQkOworQ1JFQVRFIFRSSUdHRVIgZnBvX2luc3RlYWRfdHJpZyBJTlNURUFE
IE9GIFVQREFURSBPTiBmcG9faW5zdGVhZF92aWV3CisgIEZPUiBFQUNIIFJPVyBFWEVDVVRFIEZV
TkNUSU9OIGZwb19pbnN0ZWFkX3RyaWdfZm4oKTsKK0NSRUFURSBUUklHR0VSIGZwb19pbnN0ZWFk
X2RlbF90cmlnIElOU1RFQUQgT0YgREVMRVRFIE9OIGZwb19pbnN0ZWFkX3ZpZXcKKyAgRk9SIEVB
Q0ggUk9XIEVYRUNVVEUgRlVOQ1RJT04gZnBvX2luc3RlYWRfdHJpZ19mbigpOworCitVUERBVEUg
ZnBvX2luc3RlYWRfdmlldyBGT1IgUE9SVElPTiBPRiB2YWxpZF9hdCBGUk9NICcyMDI0LTA0LTAx
JyBUTyAnMjAyNC0wOC0wMScKKyAgICBTRVQgdmFsID0gOTk5IFdIRVJFIGlkID0gMTsKK1NFTEVD
VCAqIEZST00gZnBvX2luc3RlYWRfdmlldzsKKworREVMRVRFIEZST00gZnBvX2luc3RlYWRfdmll
dyBGT1IgUE9SVElPTiBPRiB2YWxpZF9hdCBGUk9NICcyMDI0LTA0LTAxJyBUTyAnMjAyNC0wOC0w
MScKKyAgICBXSEVSRSBpZCA9IDE7CitTRUxFQ1QgKiBGUk9NIGZwb19pbnN0ZWFkX3ZpZXc7CisK
K0RST1AgVklFVyBmcG9faW5zdGVhZF92aWV3OworRFJPUCBUQUJMRSBmcG9faW5zdGVhZF9iYXNl
OworCiBSRVNFVCBkYXRlc3R5bGU7Ci0tIAoyLjQ1LjAKCg==
--00000000000076fc3c064f880a6c--