MIME-Version: 1.0
References: <ec498c3d-5f2b-48ec-b989-5561c8aa2024@illuminatedcomputing.com>
 <85ac7f0e-d95f-4377-ade0-8941fd328012@eisentraut.org>
 <CA+renyUazgR-hB_6RY60n23L0y-n_h9G1AappZmPENO0k5pL1g@mail.gmail.com>
 <7d63ddfa-c735-4dfe-8c7a-4f1e2a621058@eisentraut.org>
 <CA+renyVXg5pV84wQnGQuK8-=qoKw3BiBgQzesxM_LkcxxWmYjA@mail.gmail.com>
 <e59d218a-ae49-490f-ae6f-86c12f35d3c3@eisentraut.org>
 <CA+renyWKOj5=rMmQmJcbybu-Vdomxdp=eJ93kp76AgmQKYdfiQ@mail.gmail.com>
 <4606deaa-7d65-4f22-8a78-356c3180be9d@eisentraut.org>
 <CA+renyUhuXB2nTVCMREXew9E4DZOnFxQNjME5bcw91+k72Bosg@mail.gmail.com>
 <CA+renyWUCSyTMn3s03kviEN-oaVrJP-QkDQCLNfaY=MHV5QEiQ@mail.gmail.com>
 <CA+renyV4tWU2d=n9_v=XNPHbZfNqqLokzd-Xt78M-zLd+46ubA@mail.gmail.com>
 <53f1c094-3c29-4ef6-a9bd-dc2e7894ceb0@eisentraut.org>
 <CA+renyUSgqXpjj+vV7w+wirPB49VQFrmPjVT_s04JmZSOPNNsQ@mail.gmail.com>
 <b2ecbf9f-797f-433d-953f-3c848cde5a1c@eisentraut.org>
 <CA+renyX-eV+2hFUaZg3BSREqLE7dh+LoWm7ZqhFAiGsirjjtRQ@mail.gmail.com>
 <e2fc32a4-319b-4176-8bd8-1d974e31376f@eisentraut.org>
 <CAHg+QDckLFqthQyox2NDetYRs9sRrjmAiSA-gYRowyg8w_4vgw@mail.gmail.com>
 <CA+renyV=ryhYnxgwwWWPEk0GfHpSS_xWZVx9wmvrWozpEmnOxg@mail.gmail.com>
 <CA+renyVkfsrNNnYqLpf_g3mDV31KLFXAw-RRVmyLb7TcBLUO7A@mail.gmail.com>
 <CA+renyU5=mihx6O8+ERBmarZcuV1QiWB3X5bZhdptWoRM9G-Aw@mail.gmail.com>
In-Reply-To: 
 <CA+renyU5=mihx6O8+ERBmarZcuV1QiWB3X5bZhdptWoRM9G-Aw@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Tue, 21 Apr 2026 17:51:18 +0800
Message-ID: 
 <CACJufxH=KW9xqE9R7j3b1GiUzOvH5F6sJe9b_WjRZUJePaF=ww@mail.gmail.com>
Subject: Re: SQL:2011 Application Time Update & Delete
To: Paul A Jungwirth <pj@illuminatedcomputing.com>
Cc: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>,
 Peter Eisentraut <peter@eisentraut.org>,
	Chao Li <li.evan.chao@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CACJufxH%3DKW9xqE9R7j3b1GiUzOvH5F6sJe9b_WjRZUJePaF%3Dww%40mail.gmail.com>
Precedence: bulk

On Sun, Apr 19, 2026 at 7:18=E2=80=AFAM Paul A Jungwirth
<pj@illuminatedcomputing.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.pa=
tch

----------------------------------------------------
CREATE OR REPLACE FUNCTION trg_fponum() RETURNS TRIGGER LANGUAGE plpgsql AS
$$
BEGIN
  NEW.valid_at =3D '[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 =3D 2;
----------------------------------------------------
The above works as expected, but the below is not what i expected.

create type textrange as range (subtype =3D text, collation =3D "C");
CREATE OR REPLACE FUNCTION trg_fpo()
RETURNS TRIGGER LANGUAGE plpgsql AS
$$
BEGIN
  NEW.valid_at =3D '[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 =3D 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/