public inbox for [email protected]
help / color / mirror / Atom feedFrom: SATYANARAYANA NARLAPURAM <[email protected]>
To: Peter Eisentraut <[email protected]>
Cc: Paul A Jungwirth <[email protected]>
Cc: Chao Li <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: SQL:2011 Application Time Update & Delete
Date: Tue, 7 Apr 2026 07:32:40 -0700
Message-ID: <CAHg+QDckLFqthQyox2NDetYRs9sRrjmAiSA-gYRowyg8w_4vgw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CA+renyUazgR-hB_6RY60n23L0y-n_h9G1AappZmPENO0k5pL1g@mail.gmail.com>
<[email protected]>
<CA+renyVXg5pV84wQnGQuK8-=qoKw3BiBgQzesxM_LkcxxWmYjA@mail.gmail.com>
<[email protected]>
<CA+renyWKOj5=rMmQmJcbybu-Vdomxdp=eJ93kp76AgmQKYdfiQ@mail.gmail.com>
<[email protected]>
<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>
<[email protected]>
<CA+renyUSgqXpjj+vV7w+wirPB49VQFrmPjVT_s04JmZSOPNNsQ@mail.gmail.com>
<[email protected]>
<CA+renyX-eV+2hFUaZg3BSREqLE7dh+LoWm7ZqhFAiGsirjjtRQ@mail.gmail.com>
<[email protected]>
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%40ma...
[2]
https://www.postgresql.org/message-id/CAHg%2BQDcsXsUVaZ%2BJwM02yDRQEi%3DcL_rTH_ROLDYgOx004sQu7A%40ma...
[3]
https://www.postgresql.org/message-id/CANE55rCqcse_pwXBMWhbj3_7XROb8Dks6%3DOLFmKy3bO3zDsCsg%40mail.g...
Thanks,
Satya
On Tue, Apr 7, 2026 at 4:53 AM Peter Eisentraut <[email protected]>
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.
>
>
>
>
view thread (54+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL:2011 Application Time Update & Delete
In-Reply-To: <CAHg+QDckLFqthQyox2NDetYRs9sRrjmAiSA-gYRowyg8w_4vgw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox