Re: SQL:2011 Application Time Update & Delete

On Tue, Apr 21, 2026 at 2:51 AM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> 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).
> >
> > ...
> 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?

That looks correct to me. In the C collation, uppercase letters come
before lowercase.
The row started as '[a,d)'. Then FOR PORTION OF valid_at FROM 'A' to
'd' leaves it as '[a,d)'
(because the intersection can only narrow). Then your BEFORE trigger
changes it to '[A,d)'.

Here is a new patch though, rebased and improved in a couple ways.

First, we are able to use ExecInitForPortionOf, added by another fix.
This reduces a lot of code duplication.

Also I moved the check out of ExecForPortionOfLeftovers. Now the time
between capturing the pre-trigger value and checking it is shorter: we
do the check right after triggers fire. This also means we don't have
to add fields to ForPortionOfState.

Yours,

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