bug: UPDATE FOR PORTION OF interact with updatable view
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
Attachments:
v1-0001-UPDATE-FOR-PORTION-OF-interact-with-updatable-view.patchtext/x-patch; charset=US-ASCII; name=v1-0001-UPDATE-FOR-PORTION-OF-interact-with-updatable-view.patchDownload+49-1
On Sun, Apr 12, 2026 at 8:12 AM jian he <jian.universality@gmail.com> 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:3779The 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 ~{:-)
pj@illuminatedcomputing.com
Attachments:
v1-0001-Fix-FOR-PORTION-OF-with-non-updatable-view-column.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Fix-FOR-PORTION-OF-with-non-updatable-view-column.patchDownload+72-1
On 09.05.26 21:06, Paul A Jungwirth wrote:
On Sun, Apr 12, 2026 at 8:12 AM jian he <jian.universality@gmail.com> 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:3779The 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.
committed