Re: SQL:2011 Application Time Update & Delete

On Sun, Apr 19, 2026 at 7:18 AM Paul A Jungwirth
<pj(at)illuminatedcomputing(dot)com> wrote:
>
> Here is a patch that forbids changing the valid_at column in a BEFORE
> trigger. It works by capturing the value before triggers run, then
> checking afterwards if it is still the same (using the default btree
> equality operator; probably a simple binary comparison is good
> enough).
>
> This copy+check only happens if the table has BEFORE UPDATE row
> triggers, so there is no cost in most cases.
>
> I'm raising ERRCODE_TRIGGERED_DATA_CHANGE_VIOLATION, which is what we
> use when (basically) a trigger & UPDATE both change a row in a way
> that leaves the user intent unclear. I think that's a very close fit
> here, but you could argue we should use the same errcode as SETing
> valid_at. That is ERRCODE_SYNTAX_ERROR. That strikes me as a
> questionable choice, actually. Personally I think using different
> errcodes is correct though.
>
HI.
After applying v1-0001-Forbid-BEFORE-UPDATE-triggers-changing-the-FOR-PO.patch

----------------------------------------------------
CREATE OR REPLACE FUNCTION trg_fponum() RETURNS TRIGGER LANGUAGE plpgsql AS
$$
BEGIN
NEW.valid_at = '[1,12)';
raise notice 'old: %, new: %', old, new;
RETURN NEW;
END;
$$;
create table fpo3(valid_at int4range, b int);
CREATE TRIGGER fpo_before_update_row BEFORE UPDATE ON fpo3 FOR EACH
ROW EXECUTE PROCEDURE trg_fponum();
insert into fpo3 values('[1,100]', 1);
UPDATE fpo3 FOR PORTION OF valid_at FROM 1 TO 12 SET b = 2;
----------------------------------------------------
The above works as expected, but the below is not what i expected.

create type textrange as range (subtype = text, collation = "C");
CREATE OR REPLACE FUNCTION trg_fpo()
RETURNS TRIGGER LANGUAGE plpgsql AS
$$
BEGIN
NEW.valid_at = '[A,d)';
raise notice 'old: %, new: %', old, new;
RETURN NEW;
END;
$$;

create table fpo1(valid_at textrange, b int);
CREATE TRIGGER fpo_before_update_row BEFORE UPDATE ON fpo1 FOR EACH
ROW EXECUTE PROCEDURE trg_fpo();
insert into fpo1 values ('[a,d]', 1);

UPDATE fpo1 FOR PORTION OF valid_at FROM 'A' TO 'd' SET b = 2;
NOTICE: old: ("[a,d]",1), new: ("[A,d)",2)
ERROR: cannot change column "valid_at" from a BEFORE trigger because
it is used in FOR PORTION OF

Should I expect this to work without error, just like the table fpo3
UPDATE FOR PORTION OF statement above?

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