Re: SQL:2011 Application Time Update & Delete

Hi Peter, Paul,

Please see a few bug reports related to this at [1], [2], [3].
Additionally, it appears there is another issue here:

A BEFORE UPDATE trigger that modifies the range column creates overlapping
rows. The trigger widening the range doesn't affect leftover computation,
which uses the original FPO bounds. Result: updated row overlaps both
leftovers.

SET datestyle TO ISO, YMD;

CREATE TABLE fpo_trigger_overlap (
id int,
valid_at daterange,
val text
);

-- BEFORE UPDATE trigger that resets the range to the full year
CREATE FUNCTION widen_range() RETURNS trigger AS $$
BEGIN
NEW.valid_at := daterange('2024-01-01', '2025-01-01');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_widen BEFORE UPDATE ON fpo_trigger_overlap
FOR EACH ROW EXECUTE FUNCTION widen_range();

INSERT INTO fpo_trigger_overlap
VALUES (1, '[2024-01-01, 2025-01-01)', 'original');

UPDATE fpo_trigger_overlap
FOR PORTION OF valid_at FROM '2024-04-01' TO '2024-09-01'
SET val = 'modified';

-- Detect overlaps (should be 0 rows for correct behavior):
SELECT a.valid_at AS range_a, a.val AS val_a,
b.valid_at AS range_b, b.val AS val_b
FROM fpo_trigger_overlap a, fpo_trigger_overlap b
WHERE a.ctid < b.ctid AND a.valid_at && b.valid_at;

-- cleanup
DROP TABLE fpo_trigger_overlap;
DROP FUNCTION widen_range();

[1]
https://www.postgresql.org/message-id/CAHg%2BQDcd%3Dt69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA%3DhM0g%40mail.gmail.com

[2]
https://www.postgresql.org/message-id/CAHg%2BQDcsXsUVaZ%2BJwM02yDRQEi%3DcL_rTH_ROLDYgOx004sQu7A%40mail.gmail.com

[3]
https://www.postgresql.org/message-id/CANE55rCqcse_pwXBMWhbj3_7XROb8Dks6%3DOLFmKy3bO3zDsCsg%40mail.gmail.com

Thanks,
Satya

On Tue, Apr 7, 2026 at 4:53 AM Peter Eisentraut <peter(at)eisentraut(dot)org>
wrote:

> On 27.03.26 22:38, Paul A Jungwirth wrote:
> >> Other than all that, this patch set (0001 through 0003) seems good to
> me.
> > Thanks! These v70 patches are rebased onto f39cb8c011.
>
> I have committed the patches 0001 through 0003. (I did some editing on
> the documentation.) I think this is about as far as we can go for this
> release.
>
> Please check the follow-up bug report(?) posted in this thread.
>
>
>
>