Re: SQL:2011 Application Time Update & Delete

On Fri, Apr 10, 2026 at 3:42 AM SATYANARAYANA NARLAPURAM
<satyanarlapuram(at)gmail(dot)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 = NEW.val WHERE id = 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 = 999 WHERE id = 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 == 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/