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>
In-Reply-To: 
 <CAHg+QDcVL2d4ih5zs2Mzh63ts41N+jtnMQTdZ2_0be6aF4aqYQ@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Tue, 14 Apr 2026 12:33:07 +0800
Message-ID: 
 <CACJufxEaD_DtByjv4CZg3yKg0n6hMPThfOmTr-D6JKY+v1BJDQ@mail.gmail.com>
Subject: Re: SQL:2011 Application Time Update & Delete
To: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>,
 Paul A Jungwirth <pj@illuminatedcomputing.com>,
	Chao Li <li.evan.chao@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000ca09c1064f641aa3"
Archived-At: 
 <https://www.postgresql.org/message-id/CACJufxEaD_DtByjv4CZg3yKg0n6hMPThfOmTr-D6JKY%2Bv1BJDQ%40mail.gmail.com>
Precedence: bulk

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

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.

In RewriteQuery, we have:
/*
 * If there was no unqualified INSTEAD rule, and the target relation
 * is a view without any INSTEAD OF triggers, see if the view can be
 * automatically updated.  If so, we perform the necessary query
 * transformation here and add the resulting query to the
 * product_queries list, so that it gets recursively rewritten if
 * necessary.  For MERGE, the view must be automatically updatable if
 * any of the merge actions lack a corresponding INSTEAD OF trigger.
 *
 * If the view cannot be automatically updated, we throw an error here
 * which is OK since the query would fail at runtime anyway.  Throwing
 * the error here is preferable to the executor check since we have
 * more detailed information available about why the view isn't
 * updatable.
 */
if (!instead &&
    rt_entry_relation->rd_rel->relkind =3D=3D RELKIND_VIEW &&
    !view_has_instead_trigger(rt_entry_relation, event,
                            parsetree->mergeActionList))

Per above, RewriteQuery does not rewrite the view relation to its base
relation when the view has an INSTEAD OF trigger.
In such cases, ExecInitModifyTable->ExecInitResultRelation initialize
mtstate->resultRelInfo
using the view relation itself (rather than the underlying base table).
But ExecForPortionOfLeftovers->table_tuple_fetch_row_version requires the
relation to physical storage.

Therefore DELETE/UPDATE ... FOR PORTION OF operations cannot cope with
views that have INSTEAD OF triggers.
IMHO, rejecting it at  RewriteQuery make more sense to me.

Now the error message is:
ERROR:  UPDATE FOR PORTION OF is not supported for views with INSTEAD
OF triggers
ERROR:  DELETE FOR PORTION OF is not supported for views with INSTEAD
OF triggers


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

--000000000000ca09c1064f641aa3
Content-Type: text/x-patch; charset="US-ASCII";
	name="v10-0001-reject-instead-of-view-with-DELETE-UPDATE-FOR-PORTION-OF.patch"
Content-Disposition: attachment;
	filename="v10-0001-reject-instead-of-view-with-DELETE-UPDATE-FOR-PORTION-OF.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mny4lfuh0>
X-Attachment-Id: f_mny4lfuh0

RnJvbSBmMzdiOGQ0MWMyMTFkOGYwNWIzZmU3Yjk2YTBiZjYxODg5MGU3MTcwIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBqaWFuIGhlIDxqaWFuLnVuaXZlcnNhbGl0eUBnbWFpbC5jb20+
CkRhdGU6IFR1ZSwgMTQgQXByIDIwMjYgMTI6MTA6MDkgKzA4MDAKU3ViamVjdDogW1BBVENIIHYx
MCAxLzFdIHJlamVjdCBpbnN0ZWFkIG9mIHZpZXcgd2l0aCBERUxFVEUvVVBEQVRFIEZPUiBQT1JU
SU9OCiBPRgoKVmlld3Mgd2l0aCBJTlNURUFEIE9GIHRyaWdnZXJzIGNhbm5vdCByZXdyaXRlIHRo
ZSBxdWVyeSB0byB0aGUgYmFzZSByZWxhdGlvbi4KVmlld3MgZG8gbm90IGhhdmUgcGh5c2ljYWwg
cm93cy4gIEZPUiBQT1JUSU9OIE9GIHJlcXVpcmVzIGFjY2VzcyB0byB0aGUgcGh5c2ljYWwKcm93
IHRvIGNvbXB1dGUgdGVtcG9yYWwgbGVmdG92ZXJzLCBzbyB3ZSBtdXN0IGRpc2FsbG93IGl0IGhl
cmUuCgpkaXNjdXNzaW9uOiBodHRwczovL3Bvc3Rnci5lcy9tL0NBSGclMkJRRGQ3NGZuZDRvYkNS
TXFWUzBBVldmJTNEY1NGSCUzREN2N3RyVEpXZ20lMkJfYmhUSzZ3JTQwbWFpbC5nbWFpbC5jb20K
Y29tbWl0ZmVzdCBlbnRyeTogaHR0cHM6Ly9jb21taXRmZXN0LnBvc3RncmVzcWwub3JnL3BhdGNo
LwotLS0KIHNyYy9iYWNrZW5kL3Jld3JpdGUvcmV3cml0ZUhhbmRsZXIuYyAgICAgICAgIHwgMjgg
KysrKysrKysrKysrKysrKysrKysKIHNyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvZm9yX3BvcnRp
b25fb2Yub3V0IHwgMjEgKysrKysrKysrKysrKysrCiBzcmMvdGVzdC9yZWdyZXNzL3NxbC9mb3Jf
cG9ydGlvbl9vZi5zcWwgICAgICB8IDIzICsrKysrKysrKysrKysrKysKIDMgZmlsZXMgY2hhbmdl
ZCwgNzIgaW5zZXJ0aW9ucygrKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3Jld3JpdGUvcmV3
cml0ZUhhbmRsZXIuYyBiL3NyYy9iYWNrZW5kL3Jld3JpdGUvcmV3cml0ZUhhbmRsZXIuYwppbmRl
eCAwMjFjNzNmMWI2Ny4uZGJmYmZjYWYzNGEgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3Jld3Jp
dGUvcmV3cml0ZUhhbmRsZXIuYworKysgYi9zcmMvYmFja2VuZC9yZXdyaXRlL3Jld3JpdGVIYW5k
bGVyLmMKQEAgLTQxNjYsNiArNDE2NiwyMCBAQCBSZXdyaXRlUXVlcnkoUXVlcnkgKnBhcnNldHJl
ZSwgTGlzdCAqcmV3cml0ZV9ldmVudHMsIGludCBvcmlnX3J0X2xlbmd0aCwKIAkJCQkJCXBhcnNl
dHJlZS0+dGFyZ2V0TGlzdCA9IGxhcHBlbmQocGFyc2V0cmVlLT50YXJnZXRMaXN0LCB0bGUpOwog
CQkJCQl9CiAJCQkJfQorCQkJCWVsc2UgaWYgKHZpZXdfaGFzX2luc3RlYWRfdHJpZ2dlcihydF9l
bnRyeV9yZWxhdGlvbiwgZXZlbnQsIE5JTCkpCisJCQkJeworCQkJCQkvKgorCQkJCQkgKiBWaWV3
cyB3aXRoIElOU1RFQUQgT0YgdHJpZ2dlcnMgY2Fubm90IHJld3JpdGUgdGhlIHF1ZXJ5CisJCQkJ
CSAqIHRvIHRoZSBiYXNlIHJlbGF0aW9uLiBWaWV3cyBkbyBub3QgaGF2ZSBwaHlzaWNhbCByb3dz
LgorCQkJCQkgKiBGT1IgUE9SVElPTiBPRiByZXF1aXJlcyBhY2Nlc3MgdG8gdGhlIHBoeXNpY2Fs
IHJvdyB0bworCQkJCQkgKiBjb21wdXRlIHRlbXBvcmFsIGxlZnRvdmVycywgc28gd2UgbXVzdCBk
aXNhbGxvdyBpdAorCQkJCQkgKiBoZXJlLgorCQkJCQkgKi8KKwkJCQkJaWYgKHBhcnNldHJlZS0+
Zm9yUG9ydGlvbk9mKQorCQkJCQkJZXJlcG9ydChFUlJPUiwKKwkJCQkJCQkJZXJyY29kZShFUlJD
T0RFX09CSkVDVF9OT1RfSU5fUFJFUkVRVUlTSVRFX1NUQVRFKSwKKwkJCQkJCQkJZXJybXNnKCJV
UERBVEUgRk9SIFBPUlRJT04gT0YgaXMgbm90IHN1cHBvcnRlZCBmb3Igdmlld3Mgd2l0aCBJTlNU
RUFEIE9GIHRyaWdnZXJzIikpOworCQkJCX0KIAkJCX0KIAogCQkJcGFyc2V0cmVlLT50YXJnZXRM
aXN0ID0KQEAgLTQyMzEsNiArNDI0NSwyMCBAQCBSZXdyaXRlUXVlcnkoUXVlcnkgKnBhcnNldHJl
ZSwgTGlzdCAqcmV3cml0ZV9ldmVudHMsIGludCBvcmlnX3J0X2xlbmd0aCwKIAkJCQkJICovCiAJ
CQkJCUFkZFF1YWwocGFyc2V0cmVlLCBwYXJzZXRyZWUtPmZvclBvcnRpb25PZi0+b3ZlcmxhcHNF
eHByKTsKIAkJCQl9CisJCQkJZWxzZSBpZiAodmlld19oYXNfaW5zdGVhZF90cmlnZ2VyKHJ0X2Vu
dHJ5X3JlbGF0aW9uLCBldmVudCwgTklMKSkKKwkJCQl7CisJCQkJCS8qCisJCQkJCSAqIFZpZXdz
IHdpdGggSU5TVEVBRCBPRiB0cmlnZ2VycyBjYW5ub3QgcmV3cml0ZSB0aGUgcXVlcnkKKwkJCQkJ
ICogdG8gdGhlIGJhc2UgcmVsYXRpb24uIFZpZXdzIGRvIG5vdCBoYXZlIHBoeXNpY2FsIHJvd3Mu
CisJCQkJCSAqIEZPUiBQT1JUSU9OIE9GIHJlcXVpcmVzIGFjY2VzcyB0byB0aGUgcGh5c2ljYWwg
cm93IHRvCisJCQkJCSAqIGNvbXB1dGUgdGVtcG9yYWwgbGVmdG92ZXJzLCBzbyB3ZSBtdXN0IGRp
c2FsbG93IGl0CisJCQkJCSAqIGhlcmUuCisJCQkJCSAqLworCQkJCQlpZiAocGFyc2V0cmVlLT5m
b3JQb3J0aW9uT2YpCisJCQkJCQllcmVwb3J0KEVSUk9SLAorCQkJCQkJCQllcnJjb2RlKEVSUkNP
REVfT0JKRUNUX05PVF9JTl9QUkVSRVFVSVNJVEVfU1RBVEUpLAorCQkJCQkJCQllcnJtc2coIkRF
TEVURSBGT1IgUE9SVElPTiBPRiBpcyBub3Qgc3VwcG9ydGVkIGZvciB2aWV3cyB3aXRoIElOU1RF
QUQgT0YgdHJpZ2dlcnMiKSk7CisJCQkJfQogCQkJfQogCQl9CiAJCWVsc2UKZGlmZiAtLWdpdCBh
L3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvZm9yX3BvcnRpb25fb2Yub3V0IGIvc3JjL3Rlc3Qv
cmVncmVzcy9leHBlY3RlZC9mb3JfcG9ydGlvbl9vZi5vdXQKaW5kZXggMzFmNzcyYzcyM2QuLjll
M2MzMzdlMGUwIDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2Zvcl9wb3J0
aW9uX29mLm91dAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2Zvcl9wb3J0aW9uX29m
Lm91dApAQCAtMjA5Nyw0ICsyMDk3LDI1IEBAIFNFTEVDVCAqIEZST00gdGVtcG9yYWxfcGFydGl0
aW9uZWRfNSBPUkRFUiBCWSBpZCwgdmFsaWRfYXQ7CiAoNCByb3dzKQogCiBEUk9QIFRBQkxFIHRl
bXBvcmFsX3BhcnRpdGlvbmVkOworLS0gVGVzdDogRk9SIFBPUlRJT04gT0Ygc2hvdWxkIGJlIHJl
amVjdGVkIG9uIHZpZXdzIHdpdGggSU5TVEVBRCBPRiB0cmlnZ2VycworQ1JFQVRFIFRBQkxFIGZw
b19pbnN0ZWFkX2Jhc2UgKGlkIGludCwgdmFsaWRfYXQgZGF0ZXJhbmdlLCB2YWwgaW50KTsKK0lO
U0VSVCBJTlRPIGZwb19pbnN0ZWFkX2Jhc2UgVkFMVUVTICgxLCAnWzIwMjQtMDEtMDEsMjAyNC0x
Mi0zMSknLCAxMDApOworQ1JFQVRFIFZJRVcgZnBvX2luc3RlYWRfdmlldyBBUyBTRUxFQ1QgKiBG
Uk9NIGZwb19pbnN0ZWFkX2Jhc2U7CitDUkVBVEUgRlVOQ1RJT04gZnBvX2luc3RlYWRfdHJpZ19m
bigpIFJFVFVSTlMgdHJpZ2dlciBMQU5HVUFHRSBwbHBnc3FsIEFTICQkCitCRUdJTgorICAgIFJF
VFVSTiBORVc7CitFTkQ7CiskJDsKK0NSRUFURSBUUklHR0VSIGZwb19pbnN0ZWFkX3RyaWcgSU5T
VEVBRCBPRiBVUERBVEUgT04gZnBvX2luc3RlYWRfdmlldworICBGT1IgRUFDSCBST1cgRVhFQ1VU
RSBGVU5DVElPTiBmcG9faW5zdGVhZF90cmlnX2ZuKCk7CitDUkVBVEUgVFJJR0dFUiBmcG9faW5z
dGVhZF9kZWxfdHJpZyBJTlNURUFEIE9GIERFTEVURSBPTiBmcG9faW5zdGVhZF92aWV3CisgIEZP
UiBFQUNIIFJPVyBFWEVDVVRFIEZVTkNUSU9OIGZwb19pbnN0ZWFkX3RyaWdfZm4oKTsKK1VQREFU
RSBmcG9faW5zdGVhZF92aWV3IEZPUiBQT1JUSU9OIE9GIHZhbGlkX2F0IEZST00gJzIwMjQtMDQt
MDEnIFRPICcyMDI0LTA4LTAxJworICAgIFNFVCB2YWwgPSA5OTkgV0hFUkUgaWQgPSAxOyAgLS0g
ZXJyb3IKK0VSUk9SOiAgVVBEQVRFIEZPUiBQT1JUSU9OIE9GIGlzIG5vdCBzdXBwb3J0ZWQgZm9y
IHZpZXdzIHdpdGggSU5TVEVBRCBPRiB0cmlnZ2VycworREVMRVRFIEZST00gZnBvX2luc3RlYWRf
dmlldyBGT1IgUE9SVElPTiBPRiB2YWxpZF9hdCBGUk9NICcyMDI0LTA0LTAxJyBUTyAnMjAyNC0w
OC0wMScKKyAgICBXSEVSRSBpZCA9IDE7ICAtLSBlcnJvcgorRVJST1I6ICBERUxFVEUgRk9SIFBP
UlRJT04gT0YgaXMgbm90IHN1cHBvcnRlZCBmb3Igdmlld3Mgd2l0aCBJTlNURUFEIE9GIHRyaWdn
ZXJzCitEUk9QIFZJRVcgZnBvX2luc3RlYWRfdmlldzsKK0RST1AgVEFCTEUgZnBvX2luc3RlYWRf
YmFzZTsKIFJFU0VUIGRhdGVzdHlsZTsKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3Fs
L2Zvcl9wb3J0aW9uX29mLnNxbCBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2Zvcl9wb3J0aW9uX29m
LnNxbAppbmRleCBkNDA2MmFjZjFkMS4uZDQ2MDIxNzFjOTAgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0
L3JlZ3Jlc3Mvc3FsL2Zvcl9wb3J0aW9uX29mLnNxbAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL3Nx
bC9mb3JfcG9ydGlvbl9vZi5zcWwKQEAgLTEzNjUsNCArMTM2NSwyNyBAQCBTRUxFQ1QgKiBGUk9N
IHRlbXBvcmFsX3BhcnRpdGlvbmVkXzUgT1JERVIgQlkgaWQsIHZhbGlkX2F0OwogCiBEUk9QIFRB
QkxFIHRlbXBvcmFsX3BhcnRpdGlvbmVkOwogCistLSBUZXN0OiBGT1IgUE9SVElPTiBPRiBzaG91
bGQgYmUgcmVqZWN0ZWQgb24gdmlld3Mgd2l0aCBJTlNURUFEIE9GIHRyaWdnZXJzCitDUkVBVEUg
VEFCTEUgZnBvX2luc3RlYWRfYmFzZSAoaWQgaW50LCB2YWxpZF9hdCBkYXRlcmFuZ2UsIHZhbCBp
bnQpOworSU5TRVJUIElOVE8gZnBvX2luc3RlYWRfYmFzZSBWQUxVRVMgKDEsICdbMjAyNC0wMS0w
MSwyMDI0LTEyLTMxKScsIDEwMCk7CitDUkVBVEUgVklFVyBmcG9faW5zdGVhZF92aWV3IEFTIFNF
TEVDVCAqIEZST00gZnBvX2luc3RlYWRfYmFzZTsKK0NSRUFURSBGVU5DVElPTiBmcG9faW5zdGVh
ZF90cmlnX2ZuKCkgUkVUVVJOUyB0cmlnZ2VyIExBTkdVQUdFIHBscGdzcWwgQVMgJCQKK0JFR0lO
CisgICAgUkVUVVJOIE5FVzsKK0VORDsKKyQkOworQ1JFQVRFIFRSSUdHRVIgZnBvX2luc3RlYWRf
dHJpZyBJTlNURUFEIE9GIFVQREFURSBPTiBmcG9faW5zdGVhZF92aWV3CisgIEZPUiBFQUNIIFJP
VyBFWEVDVVRFIEZVTkNUSU9OIGZwb19pbnN0ZWFkX3RyaWdfZm4oKTsKK0NSRUFURSBUUklHR0VS
IGZwb19pbnN0ZWFkX2RlbF90cmlnIElOU1RFQUQgT0YgREVMRVRFIE9OIGZwb19pbnN0ZWFkX3Zp
ZXcKKyAgRk9SIEVBQ0ggUk9XIEVYRUNVVEUgRlVOQ1RJT04gZnBvX2luc3RlYWRfdHJpZ19mbigp
OworCitVUERBVEUgZnBvX2luc3RlYWRfdmlldyBGT1IgUE9SVElPTiBPRiB2YWxpZF9hdCBGUk9N
ICcyMDI0LTA0LTAxJyBUTyAnMjAyNC0wOC0wMScKKyAgICBTRVQgdmFsID0gOTk5IFdIRVJFIGlk
ID0gMTsgIC0tIGVycm9yCisKK0RFTEVURSBGUk9NIGZwb19pbnN0ZWFkX3ZpZXcgRk9SIFBPUlRJ
T04gT0YgdmFsaWRfYXQgRlJPTSAnMjAyNC0wNC0wMScgVE8gJzIwMjQtMDgtMDEnCisgICAgV0hF
UkUgaWQgPSAxOyAgLS0gZXJyb3IKKworRFJPUCBWSUVXIGZwb19pbnN0ZWFkX3ZpZXc7CitEUk9Q
IFRBQkxFIGZwb19pbnN0ZWFkX2Jhc2U7CisKIFJFU0VUIGRhdGVzdHlsZTsKLS0gCjIuMzQuMQoK
--000000000000ca09c1064f641aa3--