public inbox for [email protected]
help / color / mirror / Atom feedFrom: Paul A Jungwirth <[email protected]>
To: SATYANARAYANA NARLAPURAM <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Chao Li <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: SQL:2011 Application Time Update & Delete
Date: Tue, 14 Apr 2026 22:34:11 -0700
Message-ID: <CA+renyV=ryhYnxgwwWWPEk0GfHpSS_xWZVx9wmvrWozpEmnOxg@mail.gmail.com> (raw)
In-Reply-To: <CAHg+QDckLFqthQyox2NDetYRs9sRrjmAiSA-gYRowyg8w_4vgw@mail.gmail.com>
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]>
<CAHg+QDckLFqthQyox2NDetYRs9sRrjmAiSA-gYRowyg8w_4vgw@mail.gmail.com>
On Tue, Apr 7, 2026 at 7:32 AM SATYANARAYANA NARLAPURAM
<[email protected]> 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 ~{:-)
[email protected]
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: <CA+renyV=ryhYnxgwwWWPEk0GfHpSS_xWZVx9wmvrWozpEmnOxg@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