Re: SQL:2011 Application Time Update & Delete

On Mon, Apr 13, 2026 at 9:33 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> 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.

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,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com