BUG #19812: Multiple wrong-result and data-integrity issues with virtual generated columns (v18)

The following bug has been logged on the website:

Bug reference: 19812
Logged by: chunling qin
Email address: 303677365(at)qq(dot)com
PostgreSQL version: 18.6
Operating system: Linux x86_64
Description:

Virtual generated columns (new in v18) have several gaps where a code path
that does not expand the generation expression accepts a state it should
reject. All scenarios below reproduce on the official postgres:latest docker
image (18.6); each is self-contained.

1. INSERT/UPDATE accept values violating the column's typmod

CREATE TABLE b1_v (id int, a text, c varchar(3) GENERATED ALWAYS AS (a)
VIRTUAL);
INSERT INTO b1_v VALUES (1, 'abcdef'); -- accepted
SELECT * FROM b1_v; -- ERROR: value too long for
type character varying(3)

The same definition with STORED, or a virtual column with NOT NULL or
referenced by a CHECK, is rejected at INSERT time. The bare virtual column
is only evaluated at read time, so the row is stored and every subsequent
read fails (same for smallint range overflow; UPDATE and INSERT ... ON
CONFLICT DO UPDATE paths likewise).

2. Triggers see NULL instead of the computed value

CREATE TABLE tv2 (a int, b int GENERATED ALWAYS AS (a*2) VIRTUAL,
c int GENERATED ALWAYS AS (a*3) STORED, d int);

An AFTER INSERT FOR EACH ROW trigger sees NEW.b = NULL while NEW.c = 15
(STORED column correct); a statement-level trigger with REFERENCING NEW
TABLE sees NULL for b in the transition table; an ordinary SELECT returns b
= 10. A transition-table WHERE clause referencing the virtual column raises
XX000 "unexpected virtual generated column reference".

3. DROP EXPRESSION crosses inheritance boundaries it must not cross

CREATE TABLE bh_p1 (a int, b int GENERATED ALWAYS AS (a + 1) STORED);
CREATE TABLE bh_p2 (a int, b int GENERATED ALWAYS AS (a + 2) STORED);
CREATE TABLE bh_leaf (a int, b int GENERATED ALWAYS AS (a + 3) STORED)
INHERITS (bh_p1, bh_p2);
ALTER TABLE bh_p1 ALTER COLUMN b DROP EXPRESSION;
INSERT INTO bh_leaf (a) VALUES (10);
SELECT a, b FROM bh_p2; -- (10, NULL)

After the ALTER, bh_leaf.b is no longer a generated column even though
bh_p2.b still is, so inserts through bh_p2 silently read b = NULL.
PostgreSQL refuses to create this state directly (ALTER TABLE ... INHERIT of
a plain column under a generated parent errors), and sibling operations
(RENAME, ALTER COLUMN TYPE) correctly refuse to cross the multi-parent
boundary. A later DROP EXPRESSION on bh_p2 now fails because the child is no
longer generated.

4. ALTER TABLE ONLY ... SET EXPRESSION skips NOT NULL validation

CREATE TABLE vnp (a int, v int GENERATED ALWAYS AS (a) VIRTUAL NOT NULL)
PARTITION BY RANGE (a);
CREATE TABLE vnp1 PARTITION OF vnp FOR VALUES FROM (0) TO (100);
INSERT INTO vnp VALUES (1);
ALTER TABLE vnp ALTER COLUMN v SET EXPRESSION AS (nullif(a, 1)); --
ERROR: contains null values (correct)
ALTER TABLE ONLY vnp ALTER COLUMN v SET EXPRESSION AS (nullif(a, 1));
-- accepted
SELECT a, v FROM vnp; -- (1, NULL), while the column still has
attnotnull = true

Virtual columns are computed using the expression of the table referenced in
the query, so rows stored in partitions now read as NULL through the parent.
The ONLY variant validates only rows stored in the parent itself (there are
none), so the stored rows escape the check.

5. pg_dump silently loses a child's own generation expression

CREATE TABLE ep (a int, g int GENERATED ALWAYS AS (a + 1) STORED);
CREATE TABLE ec () INHERITS (ep);
ALTER TABLE ONLY ec ALTER COLUMN g SET EXPRESSION AS (a * 100);

pg_dump -s emits:

CREATE TABLE ec (
)
INHERITS (ep);

with no SET EXPRESSION - the child's column is not attislocal,
shouldPrintColumn() skips it, and unlike DEFAULT columns there is no
patch-up ALTER statement. After restore, ec.g computes (a + 1) instead of (a
* 100), silently changing values. --binary-upgrade mode dumps the expression
correctly, which locates the gap in shouldPrintColumn(). A child expression
differing from the parent is explicitly allowed by the documentation.

6. Index DDL accepts expressions reaching a virtual column via a whole-row
reference

CREATE TABLE t2 (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
CREATE FUNCTION t2_b(r t2) RETURNS int LANGUAGE sql IMMUTABLE AS $$
SELECT $1.b $$;
CREATE INDEX ON t2 ((t2_b(t2))); -- accepted
INSERT INTO t2 VALUES (1); -- ERROR: unexpected virtual
generated column reference

A direct reference (CREATE INDEX ON t2 ((b + 1))) is correctly rejected with
"indexes on virtual generated columns are not supported"; the whole-row form
slips past the check. Partial-index predicates behave the same. STORED
columns are unaffected.

Related, same whole-row-index theme, no virtual column involved:

7. Whole-row index expressions are not invalidated by ADD/DROP COLUMN

CREATE TABLE b6b (a int);
CREATE FUNCTION b6b_t(r b6b) RETURNS text LANGUAGE plpgsql IMMUTABLE AS
$$ BEGIN RETURN $1::text; END $$;
CREATE UNIQUE INDEX b6bi ON b6b ((b6b_t(b6b)));
INSERT INTO b6b VALUES (1);
ALTER TABLE b6b ADD COLUMN c int;
INSERT INTO b6b VALUES (1); -- accepted, although both rows now have
whole-row value "(1,)"
REINDEX INDEX b6bi; -- ERROR: could not create unique index
... Key ... is duplicated

The unique constraint is not enforced for the new row shape and the index is
provably corrupt; DROP COLUMN is symmetric.

Note: the partition-related facet of this family (a parent CHECK validated
against a partition's overridden generation expression) was reported
separately earlier today.