On Sat, Apr 11, 2026 at 6:01 AM 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 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/
