public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: Paul A Jungwirth <[email protected]>
Cc: SATYANARAYANA NARLAPURAM <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Chao Li <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: SQL:2011 Application Time Update & Delete
Date: Tue, 21 Apr 2026 17:51:18 +0800
Message-ID: <CACJufxH=KW9xqE9R7j3b1GiUzOvH5F6sJe9b_WjRZUJePaF=ww@mail.gmail.com> (raw)
In-Reply-To: <CA+renyU5=mihx6O8+ERBmarZcuV1QiWB3X5bZhdptWoRM9G-Aw@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CA+renyUazgR-hB_6RY60n23L0y-n_h9G1AappZmPENO0k5pL1g@mail.gmail.com>
<[email protected]>
<CA+renyVXg5pV84wQnGQuK8-=qoKw3BiBgQzesxM_LkcxxWmYjA@mail.gmail.com>
<[email protected]>
<CA+renyWKOj5=rMmQmJcbybu-Vdomxdp=eJ93kp76AgmQKYdfiQ@mail.gmail.com>
<[email protected]>
<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>
<[email protected]>
<CA+renyUSgqXpjj+vV7w+wirPB49VQFrmPjVT_s04JmZSOPNNsQ@mail.gmail.com>
<[email protected]>
<CA+renyX-eV+2hFUaZg3BSREqLE7dh+LoWm7ZqhFAiGsirjjtRQ@mail.gmail.com>
<[email protected]>
<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>
On Sun, Apr 19, 2026 at 7:18 AM Paul A Jungwirth
<[email protected]> 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/
view thread (54+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL:2011 Application Time Update & Delete
In-Reply-To: <CACJufxH=KW9xqE9R7j3b1GiUzOvH5F6sJe9b_WjRZUJePaF=ww@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox