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/
