public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: SATYANARAYANA NARLAPURAM <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: FOR PORTION OF does not recompute GENERATED STORED columns that depend on the range column
Date: Sat, 11 Apr 2026 16:36:51 +0800
Message-ID: <CACJufxHddXiF_Lx6qf7q_bkcUxQ0E7yj-Svk2DxxMUm1uspf+g@mail.gmail.com> (raw)
In-Reply-To: <CAHg+QDeGLfz8YSCChjqrxaVSrz9AnMA0NrmsNogLqeGgCt7-wg@mail.gmail.com>
References: <CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=hM0g@mail.gmail.com>
<CACJufxGreOtA-S-qeHyS5iSSsj5zZX0W3Rf8FxbyL+SVXFjLYw@mail.gmail.com>
<CAHg+QDeGLfz8YSCChjqrxaVSrz9AnMA0NrmsNogLqeGgCt7-wg@mail.gmail.com>
On Sat, Apr 11, 2026 at 6:01 AM SATYANARAYANA NARLAPURAM
<[email protected]> 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 SECURITY
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 != 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 = 'shortened';
>
> 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/
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]
Subject: Re: FOR PORTION OF does not recompute GENERATED STORED columns that depend on the range column
In-Reply-To: <CACJufxHddXiF_Lx6qf7q_bkcUxQ0E7yj-Svk2DxxMUm1uspf+g@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