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>
In-Reply-To: <e2fc32a4-319b-4176-8bd8-1d974e31376f@eisentraut.org>
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Tue, 7 Apr 2026 07:32:40 -0700
Message-ID: 
 <CAHg+QDckLFqthQyox2NDetYRs9sRrjmAiSA-gYRowyg8w_4vgw@mail.gmail.com>
Subject: Re: SQL:2011 Application Time Update & Delete
To: Peter Eisentraut <peter@eisentraut.org>
Cc: Paul A Jungwirth <pj@illuminatedcomputing.com>,
 Chao Li <li.evan.chao@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a0d268064edfa85f"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDckLFqthQyox2NDetYRs9sRrjmAiSA-gYRowyg8w_4vgw%40mail.gmail.com>
Precedence: bulk

--000000000000a0d268064edfa85f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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

[1]
https://www.postgresql.org/message-id/CAHg%2BQDcd%3Dt69gLf9yQexO07EJ2mx0Z70=
NFHo6h94X1EDA%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%3D=
OLFmKy3bO3zDsCsg%40mail.gmail.com

Thanks,
Satya

On Tue, Apr 7, 2026 at 4:53=E2=80=AFAM Peter Eisentraut <peter@eisentraut.o=
rg>
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.
>
>
>
>

--000000000000a0d268064edfa85f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Peter, Paul,<div><br></div><div>Please see a few bug re=
ports=C2=A0related to this at [1], [2], [3]. Additionally, it appears there=
 is another issue here:</div><div><br></div><div>=C2=A0A BEFORE UPDATE trig=
ger that modifies the range column creates overlapping rows. The trigger wi=
dening the range doesn&#39;t affect leftover computation, which uses the or=
iginal FPO bounds. Result: updated row overlaps both leftovers.</div><div><=
br></div><div>SET datestyle TO ISO, YMD;<br><br>CREATE TABLE fpo_trigger_ov=
erlap (<br>=C2=A0 =C2=A0 id int,<br>=C2=A0 =C2=A0 valid_at daterange,<br>=
=C2=A0 =C2=A0 val text<br>);<br><br>-- BEFORE UPDATE trigger that resets th=
e range to the full year<br>CREATE FUNCTION widen_range() RETURNS trigger A=
S $$<br>BEGIN<br>=C2=A0 =C2=A0 NEW.valid_at :=3D daterange(&#39;2024-01-01&=
#39;, &#39;2025-01-01&#39;);<br>=C2=A0 =C2=A0 RETURN NEW;<br>END;<br>$$ LAN=
GUAGE plpgsql;<br><br>CREATE TRIGGER trg_widen BEFORE UPDATE ON fpo_trigger=
_overlap<br>=C2=A0 =C2=A0 FOR EACH ROW EXECUTE FUNCTION widen_range();<br><=
br>INSERT INTO fpo_trigger_overlap<br>=C2=A0 =C2=A0 VALUES (1, &#39;[2024-0=
1-01, 2025-01-01)&#39;, &#39;original&#39;);<br><br>UPDATE fpo_trigger_over=
lap<br>=C2=A0 =C2=A0 FOR PORTION OF valid_at FROM &#39;2024-04-01&#39; TO &=
#39;2024-09-01&#39;<br>=C2=A0 =C2=A0 SET val =3D &#39;modified&#39;;<br><br=
><br>-- Detect overlaps (should be 0 rows for correct behavior):<br>SELECT =
a.valid_at AS range_a, a.val AS val_a,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0b.vali=
d_at AS range_b, b.val AS val_b<br>FROM fpo_trigger_overlap a, fpo_trigger_=
overlap b<br>WHERE a.ctid &lt; b.ctid AND a.valid_at &amp;&amp; b.valid_at;=
<br><br>-- cleanup<br>DROP TABLE fpo_trigger_overlap;<br>DROP FUNCTION wide=
n_range();</div><div><br></div><div>[1]=C2=A0<a href=3D"https://www.postgre=
sql.org/message-id/CAHg%2BQDcd%3Dt69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA%3DhM0=
g%40mail.gmail.com">https://www.postgresql.org/message-id/CAHg%2BQDcd%3Dt69=
gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA%3DhM0g%40mail.gmail.com</a></div><div><br=
></div><div>[2]=C2=A0<a href=3D"https://www.postgresql.org/message-id/CAHg%=
2BQDcsXsUVaZ%2BJwM02yDRQEi%3DcL_rTH_ROLDYgOx004sQu7A%40mail.gmail.com">http=
s://www.postgresql.org/message-id/CAHg%2BQDcsXsUVaZ%2BJwM02yDRQEi%3DcL_rTH_=
ROLDYgOx004sQu7A%40mail.gmail.com</a></div><div><br></div><div>[3]=C2=A0<a =
href=3D"https://www.postgresql.org/message-id/CANE55rCqcse_pwXBMWhbj3_7XROb=
8Dks6%3DOLFmKy3bO3zDsCsg%40mail.gmail.com">https://www.postgresql.org/messa=
ge-id/CANE55rCqcse_pwXBMWhbj3_7XROb8Dks6%3DOLFmKy3bO3zDsCsg%40mail.gmail.co=
m</a></div><div><br></div><div>Thanks,</div><div>Satya</div></div><br><div =
class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail=
_attr">On Tue, Apr 7, 2026 at 4:53=E2=80=AFAM Peter Eisentraut &lt;<a href=
=3D"mailto:peter@eisentraut.org">peter@eisentraut.org</a>&gt; wrote:<br></d=
iv><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bord=
er-left:1px solid rgb(204,204,204);padding-left:1ex">On 27.03.26 22:38, Pau=
l A Jungwirth wrote:<br>
&gt;&gt; Other than all that, this patch set (0001 through 0003) seems good=
 to me.<br>
&gt; Thanks! These v70 patches are rebased onto f39cb8c011.<br>
<br>
I have committed the patches 0001 through 0003.=C2=A0 (I did some editing o=
n <br>
the documentation.)=C2=A0 I think this is about as far as we can go for thi=
s <br>
release.<br>
<br>
Please check the follow-up bug report(?) posted in this thread.<br>
<br>
<br>
<br>
</blockquote></div>

--000000000000a0d268064edfa85f--