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

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?

jian he <jian.universality@gmail.com> writes:
> 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,

This is not an improvement over having the error happen at run time.

(1) What if the state of the underlying column changes between the
ALTER VIEW and the INSERT?  Either you have rejected something
that could have worked, or in the other direction you're going to get
the run-time error anyway.

(2) I don't see anything wrong or surprising about the run-time
error anyway, thus I fail to see that this is an improvement,
even aside from (1).

            regards, tom lane

jian he <jian.universality@gmail.com> writes:
> 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,

This is not an improvement over having the error happen at run time.

(1) What if the state of the underlying column changes between the
ALTER VIEW and the INSERT?  Either you have rejected something
that could have worked, or in the other direction you're going to get
the run-time error anyway.