disallow ALTER VIEW SET DEFAULT when the corresponding base relation column is a generated column

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: disallow ALTER VIEW SET DEFAULT when the corresponding base relation column is a generated column
Date: 2025-04-11 07:41:54
Message-ID: CACJufxHvxKd8yaV_VD-Exb0YAXtGJc_u3zrptBabi8_fUipcjA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

hi.

CREATE TABLE gtest1 (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
CREATE VIEW gtest1v AS SELECT * FROM gtest1;
ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;

INSERT INTO gtest1v VALUES (8, DEFAULT) returning *;
ERROR: cannot insert a non-DEFAULT value into column "b"
DETAIL: Column "b" is a generated column.

we can make
ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
error out,
then
INSERT INTO gtest1v VALUES (8, DEFAULT) returning *;
will work just fine.

obviously,
INSERT INTO gtest1v VALUES (8, 1) returning *;
will fail.

we can do this by in ATExecColumnDefault,
checking if
* gtest1v is updatable view or not
* column b is an updatable column or not
* column b on view corresponding base relation's column is a generated
column or not.

if all these conditions meet then, we error out saying
``cannot alter column \"%s\" on updateable view ``.

what do you think?

Attachment Content-Type Size
v1-0001-disallow-set-default-when-baserel-column-is-generated.patch text/x-patch 9.0 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Hayato Kuroda (Fujitsu) 2025-04-11 07:53:07 Regression test fails when 1) old PG is installed and 2) meson/ninja build is used
Previous Message Nisha Moond 2025-04-11 07:32:52 Re: Fix slot synchronization with two_phase decoding enabled