Re: SQL:2011 Application Time Update & Delete

On Wed, Jun 17, 2026 at 7:29 PM Paul A Jungwirth
<pj(at)illuminatedcomputing(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).
>
> 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 ~{:-)
pj(at)illuminatedcomputing(dot)com