disallow ALTER VIEW SET DEFAULT when the corresponding base relation column is a generated column
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t51417psql -h localhost -U postgresBuilt from patchset v1 (message #1), September 20, 2026 at 03:37 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t51417_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t51417_1 && git checkout t51417_1Patchset v1 (message #1) is on t51417_1
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
On Friday, April 11, 2025, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
I concur. The view is only loosely coupled to the base relation, via the
rewrite rule which is applied at runtime. Putting checks in place that
strongly couples the two relations adds a coupling burden that we are
better off avoiding.
David J.