From dfce19c2300ab00a8c41386e49dfdd6a3c8edae9 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Fri, 10 Jul 2026 10:08:21 +0200 Subject: [PATCH] Forbid FOR PORTION OF on views with INSTEAD OF triggers Previously, an attempt to use these features together caused a crash. Oversight of commit 8e72d914c528. Tests are added also to show that the check for this should be in the rewriter, not the parser, as an earlier patch version suggested. Author: Aleksander Alekseev Author: Paul A. Jungwirth Discussion: https://www.postgresql.org/message-id/flat/CAJ7c6TME%2Bix6VRf-2TPnVTsj8qn_hy6sYAOmMhZEivwsu2wS6g%40mail.gmail.com --- src/backend/rewrite/rewriteHandler.c | 8 ++ src/test/regress/expected/updatable_views.out | 71 ++++++++++++++++++ src/test/regress/sql/updatable_views.sql | 73 +++++++++++++++++++ 3 files changed, 152 insertions(+) diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index e7ae9cce65f..38f54b57eec 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -4171,6 +4171,14 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length, */ rt_entry_relation = relation_open(rt_entry->relid, NoLock); + /* We don't support FOR PORTION OF on views with INSTEAD OF triggers. */ + if (parsetree->forPortionOf && + rt_entry_relation->rd_rel->relkind == RELKIND_VIEW && + view_has_instead_trigger(rt_entry_relation, event, NIL)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("views with INSTEAD OF triggers do not support FOR PORTION OF"))); + /* * Rewrite the targetlist as needed for the command type. */ diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 7b00c742776..9c6bb2219f9 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -4165,3 +4165,74 @@ select * from base_tab order by a; drop view base_tab_view; drop table base_tab; +-- FOR PORTION OF is not supported on views with INSTEAD OF triggers +create view uv_fpo_instead_view as select id, valid_at, b from uv_fpo_tab; +create function uv_fpo_instead_trig() returns trigger language plpgsql as +$$ begin return null; end $$; +create trigger uv_fpo_instead_upd_trig + instead of update on uv_fpo_instead_view + for each row execute function uv_fpo_instead_trig(); +create trigger uv_fpo_instead_del_trig + instead of delete on uv_fpo_instead_view + for each row execute function uv_fpo_instead_trig(); +update uv_fpo_instead_view + for portion of valid_at from '2015-01-01' to '2020-01-01' + set b = 99 where id = '[1,1]'; -- error +ERROR: views with INSTEAD OF triggers do not support FOR PORTION OF +delete from uv_fpo_instead_view + for portion of valid_at from '2017-01-01' to '2022-01-01' + where id = '[1,1]'; -- error +ERROR: views with INSTEAD OF triggers do not support FOR PORTION OF +-- The check does not depend on which rows match, so it errors even when +-- no rows do. +update uv_fpo_instead_view + for portion of valid_at from '2015-01-01' to '2020-01-01' + set b = 99 where id = '[9,9]'; -- error, even with no matching rows +ERROR: views with INSTEAD OF triggers do not support FOR PORTION OF +delete from uv_fpo_instead_view + for portion of valid_at from '2017-01-01' to '2022-01-01' + where id = '[9,9]'; -- error, even with no matching rows +ERROR: views with INSTEAD OF triggers do not support FOR PORTION OF +drop view uv_fpo_instead_view; +drop function uv_fpo_instead_trig(); +-- 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. +create function uv_fpo_instead_trig() returns trigger language plpgsql as +$$ begin return new; end $$; +-- via a rewrite rule +create table uv_fpo_rule_tab (id int4range, valid_at tsrange, b float); +insert into uv_fpo_rule_tab values ('[1,1]', '[2000-01-01, 2030-01-01)', 0); +create view uv_fpo_rule_view as select id, valid_at, b from uv_fpo_rule_tab; +create rule uv_fpo_rule as on insert to uv_fpo_rule_tab do also + update uv_fpo_rule_view + for portion of valid_at from '2010-01-01' to '2020-01-01' + set b = 99 where id = '[1,1]'; +create trigger uv_fpo_rule_instead + instead of update on uv_fpo_rule_view + for each row execute function uv_fpo_instead_trig(); +-- Would crash if we checked at parse-time: +insert into uv_fpo_rule_tab values ('[2,2]', '[2000-01-01,2030-01-01)', 0); +ERROR: views with INSTEAD OF triggers do not support FOR PORTION OF +drop table uv_fpo_rule_tab cascade; +NOTICE: drop cascades to view uv_fpo_rule_view +-- via a BEGIN ATOMIC function body +create table uv_fpo_atomic_tab (id int4range, valid_at tsrange, b float); +insert into uv_fpo_atomic_tab values ('[1,1]', '[2000-01-01, 2030-01-01)', 0); +create view uv_fpo_atomic_view as select id, valid_at, b from uv_fpo_atomic_tab; +create function uv_fpo_atomic_fn() returns void language sql begin atomic + update uv_fpo_atomic_view + for portion of valid_at from '2010-01-01' to '2020-01-01' + set b = 99 where id = '[1,1]'; +end; +create trigger uv_fpo_atomic_instead + instead of update on uv_fpo_atomic_view + for each row execute function uv_fpo_instead_trig(); +-- Would crash if we checked at parse-time: +select uv_fpo_atomic_fn(); +ERROR: views with INSTEAD OF triggers do not support FOR PORTION OF +CONTEXT: SQL function "uv_fpo_atomic_fn" statement 1 +drop function uv_fpo_atomic_fn(); +drop table uv_fpo_atomic_tab cascade; +NOTICE: drop cascades to view uv_fpo_atomic_view +drop function uv_fpo_instead_trig(); diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index 4a60126ec90..2ef9aa32f36 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -2148,3 +2148,76 @@ values (1, 2, default, 5, 4, default, 3), (10, 11, 'C value', 14, 13, 100, 12); select * from base_tab order by a; drop view base_tab_view; drop table base_tab; + +-- FOR PORTION OF is not supported on views with INSTEAD OF triggers +create view uv_fpo_instead_view as select id, valid_at, b from uv_fpo_tab; + +create function uv_fpo_instead_trig() returns trigger language plpgsql as +$$ begin return null; end $$; + +create trigger uv_fpo_instead_upd_trig + instead of update on uv_fpo_instead_view + for each row execute function uv_fpo_instead_trig(); +create trigger uv_fpo_instead_del_trig + instead of delete on uv_fpo_instead_view + for each row execute function uv_fpo_instead_trig(); + +update uv_fpo_instead_view + for portion of valid_at from '2015-01-01' to '2020-01-01' + set b = 99 where id = '[1,1]'; -- error + +delete from uv_fpo_instead_view + for portion of valid_at from '2017-01-01' to '2022-01-01' + where id = '[1,1]'; -- error + +-- The check does not depend on which rows match, so it errors even when +-- no rows do. +update uv_fpo_instead_view + for portion of valid_at from '2015-01-01' to '2020-01-01' + set b = 99 where id = '[9,9]'; -- error, even with no matching rows + +delete from uv_fpo_instead_view + for portion of valid_at from '2017-01-01' to '2022-01-01' + where id = '[9,9]'; -- error, even with no matching rows + +drop view uv_fpo_instead_view; +drop function uv_fpo_instead_trig(); + +-- 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. +create function uv_fpo_instead_trig() returns trigger language plpgsql as +$$ begin return new; end $$; + +-- via a rewrite rule +create table uv_fpo_rule_tab (id int4range, valid_at tsrange, b float); +insert into uv_fpo_rule_tab values ('[1,1]', '[2000-01-01, 2030-01-01)', 0); +create view uv_fpo_rule_view as select id, valid_at, b from uv_fpo_rule_tab; +create rule uv_fpo_rule as on insert to uv_fpo_rule_tab do also + update uv_fpo_rule_view + for portion of valid_at from '2010-01-01' to '2020-01-01' + set b = 99 where id = '[1,1]'; +create trigger uv_fpo_rule_instead + instead of update on uv_fpo_rule_view + for each row execute function uv_fpo_instead_trig(); +-- Would crash if we checked at parse-time: +insert into uv_fpo_rule_tab values ('[2,2]', '[2000-01-01,2030-01-01)', 0); +drop table uv_fpo_rule_tab cascade; + +-- via a BEGIN ATOMIC function body +create table uv_fpo_atomic_tab (id int4range, valid_at tsrange, b float); +insert into uv_fpo_atomic_tab values ('[1,1]', '[2000-01-01, 2030-01-01)', 0); +create view uv_fpo_atomic_view as select id, valid_at, b from uv_fpo_atomic_tab; +create function uv_fpo_atomic_fn() returns void language sql begin atomic + update uv_fpo_atomic_view + for portion of valid_at from '2010-01-01' to '2020-01-01' + set b = 99 where id = '[1,1]'; +end; +create trigger uv_fpo_atomic_instead + instead of update on uv_fpo_atomic_view + for each row execute function uv_fpo_instead_trig(); +-- Would crash if we checked at parse-time: +select uv_fpo_atomic_fn(); +drop function uv_fpo_atomic_fn(); +drop table uv_fpo_atomic_tab cascade; +drop function uv_fpo_instead_trig(); -- 2.47.3