On Thu, Sep 3, 2026 at 7:38 PM Ewan Young <[email protected]> wrote: > > > On the other hand a DO INSTEAD NOTHING rule is allowed. Ewan's patch > > also allows DO INSTEAD NOTHING. That makes sense to me. Here is a > > revised patch adding it to Ewan's tests. > > One small thing: the new test cases use "do nothing", which the grammar > treats as DO ALSO NOTHING (opt_instead defaults to ALSO), so those rules > don't replace the query and FOR PORTION OF keeps working through the > auto-updatable path. An actual unqualified "do instead nothing" rule > sets the instead flag in fireRules(), so the patch rejects it with the > same error, for both UPDATE and DELETE. I think that is fine and > consistent with the ON CONFLICT precedent you mention, but the test > comment and the last paragraph of the commit message say the opposite, > so they should be adjusted one way or the other. (Also, the second > "do nothing" rule says "on update" where "on delete" was intended.)
Oh, good catch! I've updated the test. Even with "DO INSTEAD NOTHING", ON CONFLICT does allow the rule. I think this is important, since DO INSTEAD NOTHING has a somewhat special role as a fallback when there are other conditional rules.[0] So I'd like to make FOR PORTION OF work the same way. Making the fixed test pass required a very small code change. Here is a v3 with those edits. [0] https://www.postgresql.org/docs/current/sql-createrule.html Yours, -- Paul ~{:-) [email protected]
From 02bdf82e8669ad9a6d50e87b70b095583a9cf483 Mon Sep 17 00:00:00 2001 From: Ewan Young <[email protected]> Date: Thu, 3 Sep 2026 17:55:42 +0800 Subject: [PATCH v3] Reject FOR PORTION OF on views with unqualified INSTEAD rules A view with an unqualified DO INSTEAD rule replaces the original query with the rule's action during rewriting, which discards the query's FOR PORTION OF clause. As a result an UPDATE/DELETE ... FOR PORTION OF through such a view silently modified or deleted the entire temporal row instead of just the requested portion, with no error. Commit dfce19c2300 added the analogous guard for views with INSTEAD OF triggers; do the same for unqualified INSTEAD rules, raising the same "do not support FOR PORTION OF" feature-not-supported error. A DO ALSO rule does not replace the query, so FOR PORTION OF continues to work on the automatically-updatable path. DO INSTEAD NOTHING is allowed too, since it has no confusion about what to do. --- src/backend/rewrite/rewriteHandler.c | 11 ++++ src/test/regress/expected/updatable_views.out | 59 +++++++++++++++++++ src/test/regress/sql/updatable_views.sql | 51 ++++++++++++++++ 3 files changed, 121 insertions(+) diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 3e43418e996..74ca2a0ec84 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -4377,6 +4377,17 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length, &returning, &qual_product); + /* + * An unqualified INSTEAD rule replaces the query with the rule + * action, dropping any FOR PORTION OF clause; reject it as we do for + * views with INSTEAD OF triggers. DO INSTEAD NOTHING is fine, though. + */ + if (parsetree->forPortionOf && instead && product_queries != NIL && + rt_entry_relation->rd_rel->relkind == RELKIND_VIEW) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("views with INSTEAD rules do not support FOR PORTION OF"))); + /* * If we have a VALUES RTE with any remaining untouched DEFAULT items, * and we got any product queries, finalize the VALUES RTE for each diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 5f9adf91029..1b6adb3e10a 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -4296,6 +4296,65 @@ delete from uv_fpo_instead_view ERROR: views with INSTEAD OF triggers do not support FOR PORTION OF drop view uv_fpo_instead_view; drop function uv_fpo_instead_trig(); +-- FOR PORTION OF is likewise not supported through an unqualified INSTEAD +-- rule, which would replace the query (dropping the FOR PORTION OF clause) +-- and so modify the whole temporal row instead of the requested portion. +-- DO INSTEAD NOTHING is allowed though. +create view uv_fpo_rule_view2 as select id, valid_at, b from uv_fpo_tab; +create rule uv_fpo_rule_upd as on update to uv_fpo_rule_view2 do instead nothing; +create rule uv_fpo_rule_del as on delete to uv_fpo_rule_view2 do instead nothing; +update uv_fpo_rule_view2 + for portion of valid_at from '2021-01-01' to '2022-01-01' + set b = 99 where id = '[1,1]'; -- ok +delete from uv_fpo_rule_view2 + for portion of valid_at from '2021-01-01' to '2022-01-01' + where id = '[1,1]'; -- ok +create or replace rule uv_fpo_rule_upd as on update to uv_fpo_rule_view2 do instead + update uv_fpo_tab set b = new.b where id = old.id; +create or replace rule uv_fpo_rule_del as on delete to uv_fpo_rule_view2 do instead + delete from uv_fpo_tab where id = old.id; +update uv_fpo_rule_view2 + for portion of valid_at from '2021-01-01' to '2022-01-01' + set b = 99 where id = '[1,1]'; -- error +ERROR: views with INSTEAD rules do not support FOR PORTION OF +delete from uv_fpo_rule_view2 + for portion of valid_at from '2021-01-01' to '2022-01-01' + where id = '[1,1]'; -- error +ERROR: views with INSTEAD rules do not support FOR PORTION OF +-- As for INSTEAD OF triggers, the check does not depend on which rows match. +update uv_fpo_rule_view2 + for portion of valid_at from '2021-01-01' to '2022-01-01' + set b = 99 where id = '[9,9]'; -- error, even with no matching rows +ERROR: views with INSTEAD rules do not support FOR PORTION OF +drop view uv_fpo_rule_view2 cascade; +-- A DO ALSO rule does not replace the query, so FOR PORTION OF still works. +create table uv_fpo_also_tab (id int4range, valid_at tsrange, b float, + constraint pk_uv_fpo_also_tab primary key (id, valid_at without overlaps)); +insert into uv_fpo_also_tab values ('[1,1]', '[2020-01-01, 2030-01-01)', 0); +create view uv_fpo_also_view as select id, valid_at, b from uv_fpo_also_tab; +create table uv_fpo_also_log (t text); +create rule uv_fpo_also as on update to uv_fpo_also_view do also + insert into uv_fpo_also_log values ('updated'); +update uv_fpo_also_view + for portion of valid_at from '2022-01-01' to '2023-01-01' + set b = 88 where id = '[1,1]'; -- ok: splits the row and runs the DO ALSO action +select id, valid_at, b from uv_fpo_also_tab order by valid_at; + id | valid_at | b +-------+---------------------------------------------------------+---- + [1,2) | ["Wed Jan 01 00:00:00 2020","Sat Jan 01 00:00:00 2022") | 0 + [1,2) | ["Sat Jan 01 00:00:00 2022","Sun Jan 01 00:00:00 2023") | 88 + [1,2) | ["Sun Jan 01 00:00:00 2023","Tue Jan 01 00:00:00 2030") | 0 +(3 rows) + +select count(*) from uv_fpo_also_log; + count +------- + 1 +(1 row) + +drop view uv_fpo_also_view cascade; +drop table uv_fpo_also_log; +drop table uv_fpo_also_tab; -- Forbid INSTEAD OF triggers with FOR PORTION OF even if the FOR PORTION OF -- statement is parsed before the trigger exists. -- This can happen in at least a couple ways: a rewrite rule or a BEGIN ATOMIC function. diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index 4158bf26b74..1c8a97653d6 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -2245,6 +2245,57 @@ delete from uv_fpo_instead_view drop view uv_fpo_instead_view; drop function uv_fpo_instead_trig(); +-- FOR PORTION OF is likewise not supported through an unqualified INSTEAD +-- rule, which would replace the query (dropping the FOR PORTION OF clause) +-- and so modify the whole temporal row instead of the requested portion. +-- DO INSTEAD NOTHING is allowed though. +create view uv_fpo_rule_view2 as select id, valid_at, b from uv_fpo_tab; +create rule uv_fpo_rule_upd as on update to uv_fpo_rule_view2 do instead nothing; +create rule uv_fpo_rule_del as on delete to uv_fpo_rule_view2 do instead nothing; +update uv_fpo_rule_view2 + for portion of valid_at from '2021-01-01' to '2022-01-01' + set b = 99 where id = '[1,1]'; -- ok +delete from uv_fpo_rule_view2 + for portion of valid_at from '2021-01-01' to '2022-01-01' + where id = '[1,1]'; -- ok + +create or replace rule uv_fpo_rule_upd as on update to uv_fpo_rule_view2 do instead + update uv_fpo_tab set b = new.b where id = old.id; +create or replace rule uv_fpo_rule_del as on delete to uv_fpo_rule_view2 do instead + delete from uv_fpo_tab where id = old.id; + +update uv_fpo_rule_view2 + for portion of valid_at from '2021-01-01' to '2022-01-01' + set b = 99 where id = '[1,1]'; -- error + +delete from uv_fpo_rule_view2 + for portion of valid_at from '2021-01-01' to '2022-01-01' + where id = '[1,1]'; -- error + +-- As for INSTEAD OF triggers, the check does not depend on which rows match. +update uv_fpo_rule_view2 + for portion of valid_at from '2021-01-01' to '2022-01-01' + set b = 99 where id = '[9,9]'; -- error, even with no matching rows + +drop view uv_fpo_rule_view2 cascade; + +-- A DO ALSO rule does not replace the query, so FOR PORTION OF still works. +create table uv_fpo_also_tab (id int4range, valid_at tsrange, b float, + constraint pk_uv_fpo_also_tab primary key (id, valid_at without overlaps)); +insert into uv_fpo_also_tab values ('[1,1]', '[2020-01-01, 2030-01-01)', 0); +create view uv_fpo_also_view as select id, valid_at, b from uv_fpo_also_tab; +create table uv_fpo_also_log (t text); +create rule uv_fpo_also as on update to uv_fpo_also_view do also + insert into uv_fpo_also_log values ('updated'); +update uv_fpo_also_view + for portion of valid_at from '2022-01-01' to '2023-01-01' + set b = 88 where id = '[1,1]'; -- ok: splits the row and runs the DO ALSO action +select id, valid_at, b from uv_fpo_also_tab order by valid_at; +select count(*) from uv_fpo_also_log; +drop view uv_fpo_also_view cascade; +drop table uv_fpo_also_log; +drop table uv_fpo_also_tab; + -- Forbid INSTEAD OF triggers with FOR PORTION OF even if the FOR PORTION OF -- statement is parsed before the trigger exists. -- This can happen in at least a couple ways: a rewrite rule or a BEGIN ATOMIC function. -- 2.47.3
