On Sun, Apr 12, 2026 at 8:12 AM jian he <[email protected]> wrote:
>
> 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.
Thanks! I've attached a fix. I rewrote the DELETE error message a bit
to match the existing errors in the file more closely.
I'll make a CF entry.
Yours,
--
Paul ~{:-)
[email protected]
From 8a82a68e808600a38b8350f9d19794e0d0d3159d Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email protected]>
Date: Sat, 9 May 2026 10:47:45 -0700
Subject: [PATCH v1] Fix FOR PORTION OF with non-updatable view columns
Both UPDATE and DELETE were failing to test that the application-time
column was updatable. The column is not part of perminfo->updatedCols,
because it should not be checked for permissions. And it needs to be
checked in the DELETE case as well, since we might insert leftovers with
a value for that column.
Discussion: https://www.postgresql.org/message-id/CACJufxFRqg8%3DgbZ-Q6ZS_UQ%2BYdwfZpk%2B9rf7jgWrk8m4RMUm%3DA%40mail.gmail.com
---
src/backend/rewrite/rewriteHandler.c | 47 +++++++++++++++++++
src/test/regress/expected/updatable_views.out | 14 ++++++
src/test/regress/sql/updatable_views.sql | 11 +++++
3 files changed, 72 insertions(+)
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 77b2c9bc622..e7ae9cce65f 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3481,6 +3481,53 @@ rewriteTargetView(Query *parsetree, Relation view)
}
}
+ /*
+ * Similarly, make sure the FOR PORTION OF column is updateable. This is
+ * not included in the columns tested above, and we have to test it even
+ * for DELETEs.
+ */
+ if (parsetree->forPortionOf)
+ {
+ AttrNumber rangeAttno;
+ Bitmapset *fpo_cols;
+ char *non_updatable_col;
+ const char *fpo_update_detail;
+
+ rangeAttno = parsetree->forPortionOf->rangeVar->varattno;
+ fpo_cols = bms_make_singleton(rangeAttno - FirstLowInvalidHeapAttributeNumber);
+
+ fpo_update_detail = view_cols_are_auto_updatable(viewquery,
+ fpo_cols,
+ NULL,
+ &non_updatable_col);
+ if (fpo_update_detail)
+ {
+ switch (parsetree->commandType)
+ {
+ case CMD_UPDATE:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot update column \"%s\" of view \"%s\"",
+ non_updatable_col,
+ RelationGetRelationName(view)),
+ errdetail_internal("%s", _(fpo_update_detail))));
+ break;
+ case CMD_DELETE:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot delete from view \"%s\" using FOR PORTION OF \"%s\"",
+ RelationGetRelationName(view),
+ non_updatable_col),
+ errdetail_internal("%s", _(fpo_update_detail))));
+ break;
+ default:
+ elog(ERROR, "unrecognized CmdType: %d",
+ (int) parsetree->commandType);
+ break;
+ }
+ }
+ }
+
/*
* For MERGE, there must not be any INSTEAD OF triggers on an otherwise
* updatable view. The caller already checked that there isn't a full set
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 8852160718f..7b00c742776 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -3754,6 +3754,20 @@ 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)
+-- UPDATE/DELETE FOR PORTION fails if the column is not updatable
+-- (e.g. a computed expression, not a base column):
+create view uv_fpo_view_nonupd as
+ select id, '[1,20]'::int4range as valid_at, b
+ from uv_fpo_tab;
+-- Updating fails:
+update uv_fpo_view_nonupd for portion of valid_at from 1 to 10 set b = 2;
+ERROR: cannot update column "valid_at" of view "uv_fpo_view_nonupd"
+DETAIL: View columns that are not columns of their base relation are not updatable.
+-- Deleting fails:
+delete from uv_fpo_view_nonupd for portion of valid_at from 1 to 10;
+ERROR: cannot delete from view "uv_fpo_view_nonupd" using FOR PORTION OF "valid_at"
+DETAIL: View columns that are not columns of their base relation are not updatable.
+drop view uv_fpo_view_nonupd;
-- 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..4a60126ec90 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -1903,6 +1903,17 @@ 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;
+-- UPDATE/DELETE FOR PORTION fails if the column is not updatable
+-- (e.g. a computed expression, not a base column):
+create view uv_fpo_view_nonupd as
+ select id, '[1,20]'::int4range as valid_at, b
+ from uv_fpo_tab;
+-- Updating fails:
+update uv_fpo_view_nonupd for portion of valid_at from 1 to 10 set b = 2;
+-- Deleting fails:
+delete from uv_fpo_view_nonupd for portion of valid_at from 1 to 10;
+drop view uv_fpo_view_nonupd;
+
-- Test whole-row references to the view
create table uv_iocu_tab (a int unique, b text);
create view uv_iocu_view as
--
2.47.3
