MIME-Version: 1.0
References: 
 <CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=hM0g@mail.gmail.com>
 <CACJufxGreOtA-S-qeHyS5iSSsj5zZX0W3Rf8FxbyL+SVXFjLYw@mail.gmail.com>
 <CAHg+QDeGLfz8YSCChjqrxaVSrz9AnMA0NrmsNogLqeGgCt7-wg@mail.gmail.com>
In-Reply-To: 
 <CAHg+QDeGLfz8YSCChjqrxaVSrz9AnMA0NrmsNogLqeGgCt7-wg@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Sat, 11 Apr 2026 16:36:51 +0800
Message-ID: 
 <CACJufxHddXiF_Lx6qf7q_bkcUxQ0E7yj-Svk2DxxMUm1uspf+g@mail.gmail.com>
Subject: Re: FOR PORTION OF does not recompute GENERATED STORED columns that
 depend on the range column
To: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Cc: 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/CACJufxHddXiF_Lx6qf7q_bkcUxQ0E7yj-Svk2DxxMUm1uspf%2Bg%40mail.gmail.com>
Precedence: bulk

On Sat, Apr 11, 2026 at 6:01=E2=80=AFAM SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:
>

> Following are still failing:
>
> (1) instead of triggers + views, mentioned in the thread [2], it has both=
 the test case and the fix.
>
I will check and reply in that thread.

>
> (2) For Portion Of DELETE loses rows when a BEFORE INSERT trigger returns=
 NULL
>
> DROP TABLE IF EXISTS subscriptions CASCADE;
> CREATE TABLE subscriptions (
>     sub_id   int,
>     period   int4range NOT NULL,
>     plan     text
> );
>
> CREATE OR REPLACE FUNCTION reject_new_subscriptions() RETURNS trigger AS =
$$
> BEGIN
>     -- Business rule: no new subscription rows allowed via INSERT.
>     RETURN NULL;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER no_new_subs
>     BEFORE INSERT ON subscriptions
>     FOR EACH ROW EXECUTE FUNCTION reject_new_subscriptions();
>
> -- Pre-existing row (bypass trigger to seed it).
> ALTER TABLE subscriptions DISABLE TRIGGER no_new_subs;
> INSERT INTO subscriptions VALUES (1, '[1,100)', 'premium');
> ALTER TABLE subscriptions ENABLE TRIGGER no_new_subs;
>
> SELECT * FROM subscriptions;
> -- 1 row: (1, [1,100), premium)
>
> -- Delete just the [40,60) slice.
> DELETE FROM subscriptions FOR PORTION OF period FROM 40 TO 60;
>
> SELECT * FROM subscriptions ORDER BY period;
> -- Should be two rows: [1,40) and [60,100)
> -- Actually: 0 rows.  The whole subscription vanished.
>
> SELECT count(*) AS remaining FROM subscriptions;
> -- Expected 2, got 0.
>

I think this is expected.
https://www.postgresql.org/docs/devel/sql-delete.html says
<<>>
When FOR PORTION OF is used, this can result in users who don't have INSERT
privileges firing INSERT triggers. This should be considered when using SEC=
URITY
DEFINER trigger functions.
<<>>

We first tried inserting [1,40) and [60,100), but they were rejected
and not inserted
because the trigger function reject_new_subscriptions returned NULL.

See ExecInsert:
``````
    if (resultRelInfo->ri_TrigDesc &&
        resultRelInfo->ri_TrigDesc->trig_insert_before_row)
    {
        /* Flush any pending inserts, so rows are visible to the triggers *=
/
        if (estate->es_insert_pending_result_relations !=3D NIL)
            ExecPendingInserts(estate);
        if (!ExecBRInsertTriggers(estate, resultRelInfo, slot))
            return NULL;        /* "do nothing" */
    }
``````

> (3) FPO UPDATE loses leftovers the same way
>
> -- Shorten the meeting to only [40,60).
> UPDATE room_bookings FOR PORTION OF slot FROM 40 TO 60 SET note =3D 'shor=
tened';
>
> SELECT * FROM room_bookings ORDER BY slot;
> -- Should be three rows:
> --   [1,40)   team meeting
> --   [40,60)  shortened
> --   [60,100) team meeting
> -- Actually: only the [40,60) row survives.
>

For the same reason as above, I think the current behavior is correct.


--
jian
https://www.enterprisedb.com/