Re: SQL:2011 Application Time Update & Delete - Mailing list pgsql-hackers

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