On Thu, Sep 3, 2026 at 9:46 AM Andres Freund <[email protected]> wrote: > > > IMO this is working as intended. If you replace the original query, we > > shouldn't skip just part of it and still execute one clause. If you > > wanted to keep the FOR PORTION OF, your rule would have said that. > > That makes no sense to me. The writer of an instead-of rule can't > control/predict whether the user uses FOR PORTION OF? How could one possibly > write such a rule that works both when FOR PORTION OF is used and when not? > > I think rules, except for being an implementation detail of views, are a crazy > feature that should have been removed long ago, but I don't think that really > makes the behaviour here defensible.
In the case of triggers, we could convey the FOR PORTION OF via TG_* variables.[0] My hope was that we could offer that information to rules, too. But you're right that today a rule has no way to know whether the clause appeared or not. I think trying to apply the FOR PORTION OF clause to the rule's output is impossible though. The rule could have done anything. We can't partially-apply a statement a rule asked us to replace. But I understand why we would be reluctant to allow this today, if there is no way to write a rule that responds to FOR PORTION OF. So let's forbid the combination and raise an error. I was wondering how rules deal with other "add-on" clauses. I tried ON CONFLICT DO UPDATE. That also gets rejected: [v19beta3:15432][71204] postgres=# create table t (id integer primary key, name text); CREATE TABLE [v19beta3:15432][71204] postgres=# create rule r as on insert to t do instead select 1; CREATE RULE [v19beta3:15432][71204] postgres=# insert into t (id, name) values (1, 'foo') on conflict (id) do nothing; ERROR: INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules 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. [0] https://commitfest.postgresql.org/patch/7239/ -- Paul ~{:-) [email protected]
From 3ae80acb70c3daefdb3b4fb7b5ee8d104a908d04 Mon Sep 17 00:00:00 2001 From: Ewan Young <[email protected]> Date: Thu, 3 Sep 2026 17:55:42 +0800 Subject: [PATCH v2] 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..7c61f8cb50c 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. + */ + if (parsetree->forPortionOf && instead && + 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..6adce3514fe 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 nothing; +create rule uv_fpo_rule_del as on update to uv_fpo_rule_view2 do 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..1317d3ed689 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 nothing; +create rule uv_fpo_rule_del as on update to uv_fpo_rule_view2 do 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
