Re: SQL:2011 Application Time Update & Delete

On Fri, Apr 10, 2026 at 3:42 AM 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 = 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/