On Wed, Jun 17, 2026 at 7:29 PM Paul A Jungwirth <[email protected]> wrote: > > > 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). > > 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.
I did some research on what MariaDB and DB2 do here. MariaDB allows changing the before/end values in a BEFORE trigger. It gives the same results as Postgres today. I tested with: CREATE TABLE t (id int, ds date, de date, name text, period for valid_at (ds, de)); DELIMITER // CREATE TRIGGER t_before_update_row before UPDATE ON t FOR EACH ROW BEGIN SET NEW.ds = '2001-01-01'; END; // DELIMITER ; insert into t values (1, '2020-01-01', '2030-01-01', 'hi'); update t for portion of valid_at from '2021-06-01' to '2021-01-01' set ds = '2001-01-01'; -- fails update t for portion of valid_at from '2020-06-01' to '2021-01-01' set name = 'two'; -- okay select * from t; The result was: +------+------------+------------+------+ | id | ds | de | name | +------+------------+------------+------+ | 1 | 2001-01-01 | 2021-01-01 | two | | 1 | 2020-01-01 | 2020-06-01 | hi | | 1 | 2021-01-01 | 2030-01-01 | hi | +------+------------+------------+------+ Note that if you add a temporal primary key, truncate, and try again, the previously-allowed command will fail, because the PK blocks the duplicate. In DB2, the change is not allowed. I tested against the Community Edition for Docker and got an error. I couldn't even define the trigger! And the docs at https://www.ibm.com/docs/en/db2/11.5.x?topic=statements-create-trigger say: > BUSINESS_TIME period columns: The start and end columns of a BUSINESS_TIME > period cannot be changed in the body of BEFORE UPDATE trigger (SQLSTATE > 42808). We can't really do what DB2 does, because we don't have PERIODs (yet). So we have to wait 'til the UPDATE FOR PORTION OF to detect the problem. For us, the error is at runtime, not at trigger definition time. I'm okay with either behavior. I haven't found a rule in the standard. I feel that DB2 is more correct, but I also think the user is trying to do something weird here, and if they get weird results it is okay. If they have a primary key or unique constraint, it still blocks duplicates. If we don't want to implement the check from my previous patch, that is okay with me. Here is a v3 alternative which just adds tests showing what Postgres does. Yours, -- Paul ~{:-) [email protected]
v3-0001-Test-what-BEFORE-UPDATE-triggers-do-to-FOR-PORTIO.patch
Description: Binary data
