hi.
CREATE TABLE base_tbl (a int, b int, c int);
INSERT INTO base_tbl values(1,2);
CREATE VIEW rw_view15 AS SELECT a, ('[' || abs(b) ||
',20]')::int4range as b FROM base_tbl;
UPDATE rw_view15 for portion of b from 1 to 10 set a = 2;
DELETE FROM rw_view15 for portion of b from 1 to 10;
The UPDATE will result
ERROR: attribute number 2 not found in view targetlist
\errverbose
ERROR: XX000: attribute number 2 not found in view targetlist
LOCATION: rewriteTargetView, rewriteHandler.c:3779
The DELETE will crash the server.
Both should result in an error in rewriteTargetView.
for UPDATE, the error message:
ERROR: cannot update column "b" of view "rw_view15"
DETAIL: View columns that are not columns of their base relation are
not updatable.
for DELETE, the error message:
ERROR: DELETE ... FOR PORTION OF is not supported for column "b" on
view "rw_view15"
DETAIL: View columns that are not columns of their base relation are
not updatable.
Later, I will add this to
https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items
--
jian
https://www.enterprisedb.com/
From 7c7d5c99f05c4c9ecec0adb3acc6efc5b6cecd85 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Sun, 12 Apr 2026 23:01:31 +0800
Subject: [PATCH v1 1/1] UPDATE FOR PORTION OF interact with updatable view
discussion: https://postgr.es/m/
commitfest entry: https://commitfest.postgresql.org/patch/
---
src/backend/rewrite/rewriteHandler.c | 34 +++++++++++++++++++
src/test/regress/expected/updatable_views.out | 10 +++++-
src/test/regress/sql/updatable_views.sql | 7 +++-
3 files changed, 49 insertions(+), 2 deletions(-)
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 12dcb947f63..3cc932ea890 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3270,6 +3270,31 @@ rewriteTargetView(Query *parsetree, Relation view)
view_rte = rt_fetch(parsetree->resultRelation, parsetree->rtable);
view_perminfo = getRTEPermissionInfo(parsetree->rteperminfos, view_rte);
+
+ if (parsetree->commandType == CMD_DELETE &&
+ parsetree->forPortionOf)
+ {
+ char *non_updatable_col;
+ Bitmapset *modified_cols = NULL;
+
+ modified_cols = bms_add_member(modified_cols,
+ parsetree->forPortionOf->rangeVar->varattno - FirstLowInvalidHeapAttributeNumber);
+
+ auto_update_detail = view_cols_are_auto_updatable(viewquery,
+ modified_cols,
+ NULL,
+ &non_updatable_col);
+ if (auto_update_detail)
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DELETE ... FOR PORTION OF is not supported for column \"%s\" on view \"%s\"",
+ non_updatable_col,
+ RelationGetRelationName(view)),
+ errdetail_internal("%s", _(auto_update_detail)));
+ }
+ }
+
/*
* Are we doing INSERT/UPDATE, or MERGE containing INSERT/UPDATE? If so,
* various additional checks on the view columns need to be applied, and
@@ -3333,10 +3358,19 @@ rewriteTargetView(Query *parsetree, Relation view)
* updatable. But rewriteTargetListIU can also remove entries if they
* are DEFAULT markers and the column's default is NULL, so
* considering only the targetlist would also be wrong.
+ *
+ * Since updatedCols doesn't track the modification of the FOR PORTION
+ * OF column, we must manually add it to modified_cols.
*/
modified_cols = bms_union(view_perminfo->insertedCols,
view_perminfo->updatedCols);
+ if (parsetree->forPortionOf)
+ {
+ modified_cols = bms_add_member(modified_cols,
+ parsetree->forPortionOf->rangeVar->varattno - FirstLowInvalidHeapAttributeNumber);
+ }
+
foreach(lc, parsetree->targetList)
{
TargetEntry *tle = (TargetEntry *) lfirst(lc);
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 8852160718f..9a19ac96b69 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -3722,7 +3722,7 @@ select * from uv_iocu_tab;
drop view uv_iocu_view;
drop table uv_iocu_tab;
--- Check UPDATE FOR PORTION OF works correctly
+-- Check UPDATE/DELETE FOR PORTION OF works correctly
create table uv_fpo_tab (id int4range, valid_at tsrange, b float,
constraint pk_uv_fpo_tab primary key (id, valid_at without overlaps));
insert into uv_fpo_tab values ('[1,1]', '[2020-01-01, 2030-01-01)', 0);
@@ -3754,6 +3754,14 @@ select * from uv_fpo_view order by id, valid_at;
0 | 1 | ["Sat Jan 01 00:00:00 2022","Tue Jan 01 00:00:00 2030") | [1,2) | 2.0
(3 rows)
+create table fpo_base (a int, b int, c int);
+create view fpo_basev1 as select a, ('[' || abs(b) || ',20]')::int4range as b from fpo_base;
+update fpo_basev1 for portion of b from 1 to 10 set a = 2; -- error
+ERROR: cannot update column "b" of view "fpo_basev1"
+DETAIL: View columns that are not columns of their base relation are not updatable.
+delete from fpo_basev1 for portion of b from 1 to 10; -- error
+ERROR: DELETE ... FOR PORTION OF is not supported for column "b" on view "fpo_basev1"
+DETAIL: View columns that are not columns of their base relation are not updatable.
-- Test whole-row references to the view
create table uv_iocu_tab (a int unique, b text);
create view uv_iocu_view as
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
index f7646999bd4..8762b62df28 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -1889,7 +1889,7 @@ select * from uv_iocu_tab;
drop view uv_iocu_view;
drop table uv_iocu_tab;
--- Check UPDATE FOR PORTION OF works correctly
+-- Check UPDATE/DELETE FOR PORTION OF works correctly
create table uv_fpo_tab (id int4range, valid_at tsrange, b float,
constraint pk_uv_fpo_tab primary key (id, valid_at without overlaps));
insert into uv_fpo_tab values ('[1,1]', '[2020-01-01, 2030-01-01)', 0);
@@ -1903,6 +1903,11 @@ select * from uv_fpo_view order by id, valid_at;
delete from uv_fpo_view for portion of valid_at from '2017-01-01' to '2022-01-01' where id = '[1,1]';
select * from uv_fpo_view order by id, valid_at;
+create table fpo_base (a int, b int, c int);
+create view fpo_basev1 as select a, ('[' || abs(b) || ',20]')::int4range as b from fpo_base;
+update fpo_basev1 for portion of b from 1 to 10 set a = 2; -- error
+delete from fpo_basev1 for portion of b from 1 to 10; -- error
+
-- Test whole-row references to the view
create table uv_iocu_tab (a int unique, b text);
create view uv_iocu_view as
--
2.34.1
