Re: SQL:2011 Application Time Update & Delete

On Tue, Apr 7, 2026 at 7:32 AM SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:
>
> Hi Peter, Paul,
>
> Please see a few bug reports related to this at [1], [2], [3].

Thanks for collecting all these bugs together and for already working
on patches for them! I've started going through them; I'll respond to
each thread individually.

For the bug here (widening the range in a BEFORE UPDATE trigger), see below:

> 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();

I'm working on a fix for this. It's not quite ready, but I can finish
it in the morning. . . .

Yours,

--
Paul              ~{:-)
pj@illuminatedcomputing.com