MIME-Version: 1.0
References: <ec498c3d-5f2b-48ec-b989-5561c8aa2024@illuminatedcomputing.com>
 <85ac7f0e-d95f-4377-ade0-8941fd328012@eisentraut.org>
 <CA+renyUazgR-hB_6RY60n23L0y-n_h9G1AappZmPENO0k5pL1g@mail.gmail.com>
 <7d63ddfa-c735-4dfe-8c7a-4f1e2a621058@eisentraut.org>
 <CA+renyVXg5pV84wQnGQuK8-=qoKw3BiBgQzesxM_LkcxxWmYjA@mail.gmail.com>
 <e59d218a-ae49-490f-ae6f-86c12f35d3c3@eisentraut.org>
 <CA+renyWKOj5=rMmQmJcbybu-Vdomxdp=eJ93kp76AgmQKYdfiQ@mail.gmail.com>
 <4606deaa-7d65-4f22-8a78-356c3180be9d@eisentraut.org>
 <CA+renyUhuXB2nTVCMREXew9E4DZOnFxQNjME5bcw91+k72Bosg@mail.gmail.com>
 <CA+renyWUCSyTMn3s03kviEN-oaVrJP-QkDQCLNfaY=MHV5QEiQ@mail.gmail.com>
 <CA+renyV4tWU2d=n9_v=XNPHbZfNqqLokzd-Xt78M-zLd+46ubA@mail.gmail.com>
 <53f1c094-3c29-4ef6-a9bd-dc2e7894ceb0@eisentraut.org>
 <CA+renyUSgqXpjj+vV7w+wirPB49VQFrmPjVT_s04JmZSOPNNsQ@mail.gmail.com>
 <b2ecbf9f-797f-433d-953f-3c848cde5a1c@eisentraut.org>
 <CA+renyX-eV+2hFUaZg3BSREqLE7dh+LoWm7ZqhFAiGsirjjtRQ@mail.gmail.com>
 <e2fc32a4-319b-4176-8bd8-1d974e31376f@eisentraut.org>
 <CAHg+QDckLFqthQyox2NDetYRs9sRrjmAiSA-gYRowyg8w_4vgw@mail.gmail.com>
In-Reply-To: 
 <CAHg+QDckLFqthQyox2NDetYRs9sRrjmAiSA-gYRowyg8w_4vgw@mail.gmail.com>
From: Paul A Jungwirth <pj@illuminatedcomputing.com>
Date: Tue, 14 Apr 2026 22:34:11 -0700
Message-ID: 
 <CA+renyV=ryhYnxgwwWWPEk0GfHpSS_xWZVx9wmvrWozpEmnOxg@mail.gmail.com>
Subject: Re: SQL:2011 Application Time Update & Delete
To: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>, Chao Li <li.evan.chao@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BrenyV%3DryhYnxgwwWWPEk0GfHpSS_xWZVx9wmvrWozpEmnOxg%40mail.gmail.com>
Precedence: bulk

On Tue, Apr 7, 2026 at 7:32=E2=80=AFAM 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 overlappi=
ng rows. The trigger widening the range doesn't affect leftover computation=
, which uses the original FPO bounds. Result: updated row overlaps both lef=
tovers.
>
> 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 :=3D 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 =3D '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